Improving Performance While Filling a DataSet

Problem

Given a DataSet containing many related tables that takes a long time to fill, you need to improve the performance.

Solution

Use the EnforceConstraints property of the DataSet and the BeginLoadData( ) and EndLoadData( ) methods of the contained DataTable objects to improve performance while filling a complex DataSet .

The sample code contains one event handler and one method:

Go Button.Click

Times the filling of the DataSet created by the CreateDataSet( ) method (described next ). The EnforceConstraints property of the DataSet is set as specified and the BeginLoadData( ) and EndLoadData( ) methods of the contained DataTable objects are used, if specified. A DataAdapter is used to fill a specified DataSet with data from the Orders and Order Details tables in the Northwind database. Ten iterations are performed, and the total fill time is returned in ticks , which are 100-nanosecond intervals.

CreateDataSet( )

This method builds a DataSet containing the table schema for the Orders and Order Details tables from the Northwind database and creates a data relation between the tables. The DataSet is returned by the method.

The C# code is shown in Example 9-12.

Example 9-12. File: DataSetFillPerformanceForm.cs

// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;

// Table name constants
public const String ORDERS_TABLE = "Orders";
public const String ORDERDETAILS_TABLE = "OrderDetails";

// Field name constants for Orders table
public const String ORDERID_FIELD = "OrderID";
public const String CUSTOMERID_FIELD = "CustomerID";
public const String EMPLOYEEID_FIELD = "EmployeeID";
public const String ORDERDATE_FIELD = "OrderDate";
public const String REQUIREDDATE_FIELD = "RequiredDate";
public const String SHIPPEDDDATE_FIELD = "ShippedDate";
public const String SHIPVIA_FIELD = "ShipVia";
public const String FREIGHT_FIELD = "Freight";
public const String SHIPNAME_FIELD = "ShipName";
public const String SHIPADDRESS_FIELD = "ShipAddress";
public const String SHIPCITY_FIELD = "ShipCity";
public const String SHIPREGION_FIELD = "ShipRegion";
public const String SHIPPOSTALCODE_FIELD = "ShipPostalCode";
public const String SHIPCOUNTRY_FIELD = "ShipCountry";

// Field name constants for OrderDetails table
public const String PRODUCTID_FIELD = "ProductID";
public const String UNITPRICE_FIELD = "UnitPrice";
public const String QUANTITY_FIELD = "Quantity";
public const String DISCOUNT_FIELD = "Discount";

// Relation name constants
private const String ORDERS_ORDERDETAILS_RELATION =
 Orders_OrderDetails_Relation";

// . . . 

private void buttonGo_Click(object sender, System.EventArgs e)
{
 Cursor.Current = Cursors.WaitCursor;

 int startTick = 0;
 int totalTick = 0;

 for(int i = 0; i <= 10; i++)
 {
 // Create and fill the DataSet counting elapsed ticks.
 DataSet ds = CreateDataSet( );

 if (enforceConstraintsOffCheckBox.Checked)
 ds.EnforceConstraints = false;
 
 SqlDataAdapter da;

 // Fill the Order table in the DataSet.
 da = new SqlDataAdapter("SELECT * FROM Orders",
 ConfigurationSettings.AppSettings["Sql_ConnectString"]);
 if (loadDataCheckBox.Checked)
 ds.Tables[ORDERS_TABLE].BeginLoadData( );

 startTick = Environment.TickCount;
 da.Fill(ds, ORDERS_TABLE);
 totalTick += Environment.TickCount - startTick;

 if (loadDataCheckBox.Checked)
 ds.Tables[ORDERS_TABLE].EndLoadData( );

 // Fill the OrderDetails table in the DataSet.
 da = new SqlDataAdapter("SELECT * FROM [Order Details]",
 ConfigurationSettings.AppSettings["Sql_ConnectString"]);
 if (loadDataCheckBox.Checked)
 ds.Tables[ORDERDETAILS_TABLE].BeginLoadData( );

 startTick = Environment.TickCount;
 da.Fill(ds, ORDERDETAILS_TABLE);
 totalTick += Environment.TickCount - startTick;

 if (loadDataCheckBox.Checked)
 ds.Tables[ORDERDETAILS_TABLE].EndLoadData( );

 if (enforceConstraintsOffCheckBox.Checked)
 ds.EnforceConstraints = true;
 }

 resultTextBox.Text +=
 "Ticks = " + totalTick + "; " +
 "Enforce constraints = " +
 !enforceConstraintsOffCheckBox.Checked + "; " +
 "BeginLoadData/EndLoadData = " + loadDataCheckBox.Checked + 
 Environment.NewLine;

 Cursor.Current = Cursors.Default;
}

private DataSet CreateDataSet( )
{
 DataSet ds = new DataSet( );

 // Create the Orders table.
 DataTable dtOrders = new DataTable(ORDERS_TABLE);
 DataColumnCollection cols = dtOrders.Columns;

 // Add the identity field.
 DataColumn col = cols.Add(ORDERID_FIELD, typeof(System.Int32)); 
 col.AllowDBNull = false;
 col.AutoIncrement = true;
 col.AutoIncrementSeed = -1;
 col.AutoIncrementStep = -1;
 // Add the other fields.
 cols.Add(CUSTOMERID_FIELD, typeof(System.String)).MaxLength=5; 
 cols.Add(EMPLOYEEID_FIELD, typeof(System.Int32)); 
 cols.Add(ORDERDATE_FIELD, typeof(System.DateTime)); 
 cols.Add(REQUIREDDATE_FIELD, typeof(System.DateTime)); 
 cols.Add(SHIPPEDDDATE_FIELD, typeof(System.DateTime)); 
 cols.Add(SHIPVIA_FIELD, typeof(System.Int32)); 
 cols.Add(FREIGHT_FIELD, typeof(System.Decimal)); 
 cols.Add(SHIPNAME_FIELD, typeof(System.String)).MaxLength = 40; 
 cols.Add(SHIPADDRESS_FIELD, typeof(System.String)).MaxLength = 60; 
 cols.Add(SHIPCITY_FIELD, typeof(System.String)).MaxLength = 15; 
 cols.Add(SHIPREGION_FIELD, typeof(System.String)).MaxLength = 15; 
 cols.Add(SHIPPOSTALCODE_FIELD, typeof(System.String)).MaxLength = 10; 
 cols.Add(SHIPCOUNTRY_FIELD, typeof(System.String)).MaxLength = 15; 
 // Set the primary key.
 dtOrders.PrimaryKey = new DataColumn[] {cols[ORDERID_FIELD]};
 // Add the Orders table to the DataSet.
 ds.Tables.Add(dtOrders);

 // Create the OrderDetails table.
 DataTable dtOrderDetails = new DataTable(ORDERDETAILS_TABLE);
 cols = dtOrderDetails.Columns;

 // Add the PK fields.
 cols.Add(ORDERID_FIELD, typeof(System.Int32)).AllowDBNull = false; 
 cols.Add(PRODUCTID_FIELD, typeof(System.Int32)).AllowDBNull = false;
 // Add the other fields.
 cols.Add(UNITPRICE_FIELD, typeof(System.Decimal)).AllowDBNull = false; 
 cols.Add(QUANTITY_FIELD, typeof(System.Int16)).AllowDBNull = false; 
 cols.Add(DISCOUNT_FIELD, typeof(System.Single)).AllowDBNull = false; 
 // Set the primary key.
 dtOrderDetails.PrimaryKey = new DataColumn[]
 {
 cols[ORDERID_FIELD],
 cols[PRODUCTID_FIELD]
 };
 // Add the OrderDetails table to the DataSet.
 ds.Tables.Add(dtOrderDetails);

 // Create a relation between the tables.
 ds.Relations.Add(ORDERS_ORDERDETAILS_RELATION,
 dtOrders.Columns[ORDERID_FIELD],
 dtOrderDetails.Columns[ORDERID_FIELD],
 true);

 return ds;
}

Discussion

Filling a DataSet is slowed by the time that the DataSet spends maintaining indexes and validating integrity constraints. Performance can be improved by turning off this functionality while filling a DataSet and turning it back on once the DataSet is filled.

The EnforceConstraints property of the DataSet indicates whether constraint rulesunique and foreign key constraintsare verified when updating data in the DataSet .

Setting EnforceConstraints to false prior to loading data into a DataSet prevents the constraints on the DataSet from being validated when each row is added. Instead, when EnforceConstraints is set to true an attempt is made to enable the constraints. A ConstraintException is raised if the DataSet contains constraint violations.

The BeginLoadData( ) and EndLoadData( ) methods of the DataTable turn off notifications, index maintenance, and constraints while loading data using the LoadDataRow( ) method. These two methods must be called as each DataTable in the DataSet is loaded with data.

SQL Server Debugging and Connection Pooling

If you are running your application from the Visual Studio .NET IDE and SQL debugging is enabled, connections are not reused from the pool as quickly as usual. This can cause your application to unexpectedly run out of connections.

To control SQL debugging for a project:

1. Right-click the Project in the Solution Explorer window and click the Properties item in the submenu to open the Property Pages dialog.
2. Select Configuration Properties Debugging in the left pane of the Project Pages dialog.
3. The Enable SQL Debugging option in the right pane controls whether debugging of SQL commands is enabled.

For more information about debugging SQL Server stored procedures, see Recipe 9.8.