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.