| [ Team LiB ] |
|
Recipe 9.9 Improving Performance While Filling a DataSetProblemGiven a DataSet containing many related tables that takes a long time to fill, you need to improve the performance. SolutionUse 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:
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;
}
DiscussionFilling 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 rules—unique and foreign key constraints—are 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.
|
| [ Team LiB ] |
|
Debugging in the
left pane of the Project Pages dialog.