ADO.NET 3.5 Cookbook, 2nd Edition

10.14. 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

Investigate using 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 solution measures the time it takes to fill a DataSet using a DataAdapter with all records in the Sales.SalesOrderHeader, Sales.SalesOrderDetail, and Sales.Customer tables, related in the DataSet. The fill times are measured for all four combinations of setting the EnforceConstraints property of the DataSet and using or not using the BeginLoadData() and EndLoadData() methods of the contained DataTable objects. Ten iterations are performed, and the total fill time for each combination is returned and displayed in ticks (one-millisecond intervals).

The C# code in Program.cs in the project ImprovePerformanceFillDataSet is shown in Example 10-24.

Example 10-24. File: Program.cs for ImprovePerformanceFillDataSet solution

using System; using System.Data; using System.Data.SqlClient; namespace ImprovePerformanceFillDataSet { class Program { private static DataSet ds = new DataSet(); private static DataTable dtCustomer = new DataTable("Customer"); private static DataTable dtHeader = new DataTable("Header"); private static DataTable dtDetail = new DataTable("Detail"); private static SqlDataAdapter daCustomer; ...