ADO.NET 2.0 Performance Guidelines - New in 2.0

From Guidance Share

Jump to: navigation, search

Contents

Use SqlBulkCopy for Faster Transfer or Upload of Bulk Data

Use ADO.NET 2.0 new feature SqlBulkCopy to copy bulk data from one data source to another data source programmatically.


Why

SqlBulkCopy can be used to copy bulk data from one data source to another data source programmatically. It gives better performance for the following reasons:

  • The entire bulk copy operation is treated as one single execution call, irrespective of number of rows to be copied. This eliminates the required multiple server round-trips
  • It saves multiple statement executions overhead for each row, as the entire process is completed in a single statement call execution

Microsoft SQL Server also provides a command-prompt utility called bcp for moving data from one table to another table on the same server or different server. There are also another way to load data into a SQL Server tables using INSERT statements and DTS Packages. The SqlBulkCopy class enables to write managed code that provide similar functionality, with a significant performance advantage over other approaches.

When

When it is required to transfer or upload bulk of data from one data source to destination SQL Server Database, consider using SqlBulkCopy for better performance.

  • The SqlBulkCopy class can be used to write data only to SQL Server tables. However, the data source can be anything other then SQL Server also; as long as the data can be loaded to a DataTable instance or read with an IDataReader instance.
  • SqlBulkCopy can be used to transfer data between two tables on the same SQL Server or between two different SQL Servers.

How

Following are the steps for using SqlBulkCopy

  • Copy the data from the source database in a DataSet, DataTable, or a DataReader using the following code snippet:
// Select data from Source table
cmd = new SqlCommand("SELECT * FROM Employee", sourceDBConnectionStr);
// Execute reader
SqlDataReader reader = cmd.ExecuteReader();
  • Create an instance of SqlBulkCopy class constructor by providing destination connection string or SqlConnection object. Assign the destination table name value to the DestinationTableName Property as follows:
// Create SqlBulkCopy
SqlBulkCopy bulkData = new SqlBulkCopy(destDBConnectionString);
// Set target table name
bulkData.DestinationTableName = "EmployeeBackup"; 
  • Use SqlBulkCopy class WriteToServer () method to write data from a DataReader, DataSet, or DataTable to the destination data source.
bulkData.WriteToServer(reader);
  • Finally, invoke Close () method of the SqlBulkCopy class to release occupied system resources:
bulkData.Close();


Problem Example

A Windows Console application programmatically transfers data from a Comma Separated Values (CSV) file to the SQL Server Database Table. The application will first read the CSV file, create a DataTable and then use Data Adapter's Update() method to complete the operation. The Update() Method internally makes multiple server round-trips to transfer all the rows from DataTable to destination SQL Server Database Table, which degrades the performance of the application. The following code loads the data from the CSV file int a DataTable :

StreamReader streamReader= new StreamReader(filePath);
string fileContentStr = streamReader.ReadToEnd();
streamReader.Close();
streamReader.Dispose();            

string[] rows = fileContentStr.Split('\n');

DataTable dt = new DataTable();
string[] columns =rows[0].Split(',');

//Create Columns in the DataTable
foreach(string col in columns)
{
  dt.Columns.Add(new DataColumn());
}

//Create Rows in the DataTable
DataRow row;
string lastLine = "";
foreach (string record in rows)
{
  row= dt.NewRow();
  lastLine = record.Replace(Convert.ToString('\r'), "");
  row.ItemArray = lastLine.Split(',');                
  dt.Rows.Add(row);    
}   

Once the data is available in a DataTable, the Data Adapter's Update () method is used to transfer the data to the destination SQL Server Database table. The following code snippet illustrates the problem:

SqlConnection conn = new SqlConnection("destDBConnectionString");
SqlDataAdapter adapter= new SqlDataAdapter();
SqlParameter param;

// Set up the Insert Command
SqlCommand insertCommand = new SqlCommand(
   "INSERT into EmployeeBackup(EmployeeID, EmployeeName) " +
   "VALUES (@EmployeeID, @EmployeeName)", conn);
param = new SqlParameter("@EmployeeID", SqlDbType.Int);
param.SourceVersion = DataRowVersion.Current;
param.SourceColumn = "EmployeeID";
insertCommand.Parameters.Add(param);
param = new SqlParameter("@EmployeeName", SqlDbType.NChar, 80);
param.SourceVersion = DataRowVersion.Current;
param.SourceColumn = "EmployeeName";
insertCommand.Parameters.Add(param);

//set the INSERT command of Adapter
adapter.InsertCommand = insertCommand;

//Finally, update the database
adapter.Update(dt);

Solution Example

A Windows Console application programmatically transfers data from a Comma Separated Values (CSV) file to the SQL Server Database Table. The application will first read the CSV file, create a DataTable and then use SqlBulkCopy feature to transfer all the rows from DataTable to destination SQL Server Database Table. This operation is much faster as the multiple server round-trips are eliminated.

The following code loads the data from the CSV file int a DataTable :

StreamReader streamReader= new StreamReader(filePath);
string fileContentStr = streamReader.ReadToEnd();
streamReader.Close();
streamReader.Dispose();            

string[] rows = fileContentStr.Split('\n');

DataTable dt = new DataTable();
string[] columns =rows[0].Split(','); 

//Create Columns in the DataTable
foreach(string col in columns)
{
  dt.Columns.Add(new DataColumn());
}

//Create Rows in the DataTable
DataRow row;
string lastLine = "";
foreach (string record in rows)
{
  row= dt.NewRow();
  lastLine = record.Replace(Convert.ToString('\r'), "");
  row.ItemArray = lastLine.Split(',');                
  dt.Rows.Add(row);    
}   

Once the data is available in a DataTable, the SqlBulkCopy feature is used to transfer the data to the destination SQL Server Database table as follows:

SqlConnection cn = new SqlConnection("destDBConnectionString");
SqlBulkCopy bc = new SqlBulkCopy(cn, SqlBulkCopyOptions.TableLock,null);
bc.BatchSize = dt.Rows.Count;
cn.Open();
bc.DestinationTableName = "Employee";
bc.WriteToServer(dt);
cn.Close();
bc.Close();  

References


Use Asynchronous Command Execution

Set the value for async attribute to true in the connection string to use asynchronous database operations through SqlCommand class of ADO.NET.

If required use appropriate mechanism from the following options to handle the asynchronous execution.

  • Use WaitAny or WaitAll method of WaitHandle class to handle execution of one or more asynchronous SQL commands.
  • Use the IsCompleted property of IAsyncResult interface to find out whether or not the asynchronous database operation has completed
  • Define a delegate in the BeginExecuteNonQuery, BeginExecuteReader. BeginExecuteXmlReader method using AsyncCallback delegate, which can be called when the operation is complete.


Why

Asynchronous command execution feature of SqlCommand class allows client to execute the next line of code immediately after invoking the SQL command without waiting for a response from the database. This means client code can perform some other operations while the database server is executing the query to improve the performance and user responsiveness.


When

When the application has to do certain activities during the execution of the query, consider asynchronous database processing. Some of the scenarios would be as follows

  • When you want to start a database operation and continue running other processes without waiting for the database operation to complete
  • Keep user interface thread to remain responsive during execution of a long running database call.
  • When you want to execute multiple concurrent commands. Example – when you need data from multiple databases.

Note Asynchronous database processing can be used only if the application is running on Microsoft Windows XP and Windows 2000, not in Windows 98 or Windows ME


How

ADO.NET supports asynchronous database operations through its SqlCommand class. BeginExecuteNonQuery, BeginExecuteReader, and BeginExecuteXmlReader methods along with the EndExecuteNonQuery, EndExecuteReader, and EndExecuteXmlReader methods of SqlCommand class provide the asynchronous execution support. Following are the steps for Asynchronous command execution

  • Enable the asynchronous process by setting async attribute to true in the connection string.
String connStr= "server=myserver;database=mydb;integrated security=true;async=true"
  • Define connection object for the database, open the connection and start the execution of the query
SqlCommand cmdAcc = new SqlCommand (commandText1, connection1);
//Use  BeginExecuteReader or BeginExecuteNonQuery 
// or BeginExecuteXmlReader to  start the execution
IAsyncResult arAcc = cmdAcc.BeginExecuteReader();
  • Write code for doing some other parallel activities which can be performed while the above query is executing on the server.
  • End the execution of the query
//End the execution
SqlDataReader drAcc = cmdAcc.EndExecuteReader(arAcc);
  • If required use WaitAny or WaitAll method of WaitHandle class to handle execution of asynchronous SQL commands. The WaitAny and WaitAll methods both require WaitHandle objects as an argument, grouped together in an array.
// Get the wait handles and build an array
WaitHandle waitHandle1 = arAcc.AsyncWaitHandle;
WaitHandle waitHandle2 = arHR.AsyncWaitHandle;
WaitHandle[] waitHandles = {waitHandle1, waitHandle2, waitHandle3};

// WaitAll waits for all of the processes to complete. 
// The return value is true if the all processes completed 
// successfully, False if any process timed out

bool result = WaitHandle.WaitAll(waitHandles, 60000, false);

// WaitAny waits for any of the processes to complete. 
// The return value is either the index of the array element 
// whose process just completed, or the WaitTimeout value

int index;
for (int countWaits = 0; countWaits <= 2; countWaits++)
{
    index = WaitHandle.WaitAny(waitHandles, 60000, false);
}
  • If required use the IsCompleted property to find out whether or not the asynchronous database operation has completed
IAsyncResult arAcc = cmdAcc.BeginExecuteReader();
while (!arAcc.IsCompleted)
{
    // Wait 
}
  • A callback can be defined by specifying an AsyncCallback delegate in the BeginExecuteNonQuery, BeginExecuteReader, or BeginExecuteXmlReader method. The delegate is called when the operation is complete. Delegate can be passed as a reference to the SqlCommand itself, making it easy to access the SqlCommand object and call the appropriate End method without having to use a global variable.
AsyncCallback callback = new AsyncCallback(HandleCallback);

// Start the execute query from the regular function and end the 
// execute query from callback function.
command.BeginExecuteNonQuery(callback, command);

private void HandleCallback(IAsyncResult arAcc)
{
      // Retrieve the original command object, passed
      // to this procedure in the AsyncState property
      // of the IAsyncResult parameter.
     SqlCommand cmdAcc = (SqlCommand) arAcc.AsyncState;
     SqlDataReader drAcc = cmdAcc.EndExecuteNonQuery(arAcc);

}


Problem Example

A .NET application needs to display information about a particular employee. Required information resides in various databases like Accounting, HR, etc. Application brings these in a traditional sequential way. This causes to reduce the performance and user responsiveness of the application.

String connStrAcc= "server=myserver;database=Accdb;integrated security=true;"
String connStrHR= "server=myserver;database=HRdb;integrated security=true;"
SQLConnection connAcc = new SQLConnection(connStrAcc);
SQLConnection connHR = new SQLConnection(connStrHR);
connAcc.Open();
SqlCommand cmdAcc = new SqlCommand(
  "SELECT * FROM EMPLOYEE_INFO", connAcc);
SqlDataReader drAcc = cmdAcc. ExecuteReader();

//Execution of query for HR database starts only 
// after coming back from Account database
connHR.Open();
SqlCommand cmdHR = new SqlCommand(
   "SELECT * FROM EMPLOYEE_HRINFO", connHR);
SqlDataReader drHR = cmdHR.ExecuteReader();


Solution Example

A .NET application needs to display information about a particular employee. Required information resides in various databases like Accounting, HR, etc. Asynchronous command execution is used to parallelize the execution of multiple database queries, thus improving the performance of the application.

//Enable Asynchronous command execution
String connStrAcc= "server=myserver;database=Accdb;integrated security=true; async=true"
String connStrHR= "server=myserver;database=HRdb;integrated security=true;  async=true"

SQLConnection connAcc = new SQLConnection(connStrAcc);
SQLConnection connHR = new SQLConnection(connStrHR);

connAcc.Open();

SqlCommand cmdAcc = new SqlCommand("SELECT * FROM EMPLOYEE_INFO", connAcc);

//Start the execution of the first query 
IAsyncResult arAcc = cmdAcc.BeginExecuteReader();

// At this point the query for EMPLOYEE_INFO is executing on the server 
// and this thread is running at the same time and continues to execute 
// the query for HR database
connHR.Open();

SqlCommand cmdHR = new SqlCommand("SELECT * FROM EMPLOYEE_HRINFO", connHR);
IAsyncResult arHR = cmdHR.BeginExecuteReader();

//Now both the queries are running at the same time and also 
//some other  work can be done from this thread at this time.

//End executions
SqlDataReader drAcc = cmdAcc. EndExecuteReader(arAcc);
SqlDataReader drHR = cmdHR. EndExecuteReader(arHR);


References


Use Data Adapter Update Batching to Reduce Number of Server Round-trips

SqlDataAdapter and OracleDataAdapter class in ADO.NET 2.0 has a new property, called UpdateBatchSize. Use UpdateBatchSize property to specify the number-of SqlCommand to be sent in single round-trip to the database server. Set UpdateBatchSize property value other than 1 (default) to enable Update Batching feature of ADO.Net 2.0.


Why

The UpdateBatchSize property allows to specify number of commands that will be packaged together and sent to the database in a single round-trip. The Update Batching feature provides better performance as:

  • It reduces the number of server round-trips between SqlDataAdapter and Database Server
  • It optimizes the execution process of DataAdapter.Update() call at lower-level

The previous versions of ADO.NET Data Adapter forces fixed one round trip per SqlCommand. The Data Adapter’s Update() method cycles-through each row-change in the DataTable and executes a corresponding command - InsertCommand, UpdateCommand or DeleteCommand separately. Since, each execution is independent; the application keeps going back and forth over the network between the client and the server, which degrades performance.


When

When it is required to submit multiple row-changes to Database, consider enabling update batching feature of Data Adapter.


How

To enable the Update Batching Feature, set UpdateBatchSize property of SqlDataAdapter or OracleDataAdapter class to a value other than 1. The UpdateBatchSize property allows to assign number-of SqlCommands that will be sent to the database with each server round-trip:

  • UpdateBatchSize = 1 (default), disables Update Batching
  • UpdateBatchSize = N where N > 1, sends N commands at a time
  • UpdateBatchSize = 0, sends the all commands at a time

Before calling the Data Adapter's Update () method, set appropriate value of UpdateBatchSize Property:

...
// Send all SqlCommands in single server Round-trip 
adapter.UpdateBatchSize = 0; 
adapter.Update(ds);
...

In order to verify that the number-of round-trips have reduced, we can hook into the DataRowUpdating and DataRowUpdated events of the DataAdapter as follows:

...
adapter.RowUpdating += new SqlRowUpdatingEventHandler(adapter_RowUpdating);
adapter.RowUpdated += new SqlRowUpdatedEventHandler(adapter_RowUpdated);
    
private void adapter_RowUpdated(object sender, SqlRowUpdatedEventArgs e)
{
    _countUpdated++;
}

void adapter_RowUpdating(object sender, SqlRowUpdatingEventArgs e)
{
    _countUpdating++;
}
...

Without Update Batching, both events will fire N times. However, with Update Batching, RowUpdated will only be called once for each batch.


Problem Example

A Windows Forms Employee Management data-driven application, retrieves employee records in DataSet from the database server and presents to the Admin User in the Datagrid type-of user interface. The Admin User modifies several employees' records, and then finally wants to submit the changes to the database server. To save all the changes performed by the user, it is required to perform multiple database server round-trips, which degrades the application performance. The following code snippet illustrates the problem for the scenario:

SqlConnection connection = new SqlConnection("...");
SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Employee", connection);
DataSet dsEmployee = new DataSet();
adapter.Fill(dsEmployee);
//Simulating the DataSet Updates by changing the EmployeeName Column
foreach (DataRow dr in ds.Tables[0].Rows)
{    
   string employeeName = dr["EmployeeName"].ToString();
   dr["EmployeeName"] = employeeName;
}
//Prepare the UpdateCommand to submit the changes to the database
SqlCommand command = new SqlCommand();
command.CommandText = "Update EmployeeName Set EmployeeName = @EmployeeName WHERE EmployeeID = @EmployeeID";
command.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int)).SourceColumn = "EmployeeID";
command.Parameters.Add(new SqlParameter("@ColumnName", SqlDbType.NVarChar, 50)).SourceColumn = "ColumnName";
adapter.UpdateCommand = command;
//Finally, submit the changes to the database server
adapter.Update(dsEmployee);


Solution Example

A Windows Forms Employee Management data-driven application, retrieves employee records in DataSet from the database server and presents to the Admin User in the Datagrid type-of user interface. The Admin User modifies several employees' records, and then finally wants to submit the changes to the database server. The multiple server round-trips are avoided by enabling Update Batching feature of ADO.Net 2.0 SqlDataAdapter. To execute all the SqlCommand in a single server trip for maximum performance, set the UpdateBatchSize property of the SqlDataAdapter class to 0.

SqlConnection connection = new SqlConnection("...");
SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Employee", connection);
DataSet dsEmployee = new DataSet();
adapter.Fill(dsEmployee);
//Simulating the DataSet Updates by changing the EmployeeName Column
foreach (DataRow dr in ds.Tables[0].Rows)
{
   string employeeName = dr["EmployeeName"].ToString();
   dr["EmployeeName"] = employeeName;
}
//Prepare the UpdateCommand to submitt the changes to the database
SqlCommand command = new SqlCommand();
command.CommandText = "Update EmployeeName Set EmployeeName = @EmployeeName WHERE      EmployeeID = @EmployeeID";
command.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int)).SourceColumn     = "EmployeeID";
command.Parameters.Add(new SqlParameter("@ColumnName", SqlDbType.NVarChar, 50)).SourceColumn = "ColumnName";
adapter.UpdateCommand = command;
//Finally, submit the changes to the database server
adapter.UpdateBatchSize = 0;
adapter.Update(dsEmployee);


Additional Resources


Use MARS as an alternative to opening several connections

Enable MARS (Multiple Active Result Sets) feature for connecting to SQL Server 2005 while interleaved execution of multiple requests (batches) within a single database connection is required.

  • Enable MARS by setting the value for MultipleActiveResultSets attribute to true in the connection string with the SqlClient.NET Data Provider
  • Enable MARS using SQLSetConnectAttr function with the SQL Native Client ODBC driver
  • Enable MARS using the DBPROPSET_SQLSERVERDBINIT property with the SQL Native Client OLE DB Provider

Why

The MARS infrastructure allows interleaved execution of multiple requests (batches) within a single connection with the help of multiple default result sets. Therefore it allows a batch to run and, within the execution, allows other requests (for example, INSERT, UPDATE, DELETE, and stored procedure calls) to execute. This helps to improve the performance and to reduce the possibility of occurring deadlock situations.

When

Use MARS feature in .NET application for connecting to SQL Server 2005

  • Where your application should have the capability to perform multiple operations on a single database connection
  • To replace currently using server cursors as a workaround for a lack of MARS
  • To replace currently using multiple connections options as a workaround for a lack of MARS
  • MARS can be used where the database is SQL server 2005 and MARS enabled client drivers are used in the application. Following are MARS enabled client drivers.

* The SqlClient.NET Data Provider included in the Microsoft.NET Framework, Version 2.0 * The SQLODBC driver included in the SQL Native Client. * The SQLOLEDB driver included in the SQL Native Client. Note MARS does not support parallel execution and its not a replacement for server cursors.

How

Following are various method for enabling MARS

  • Enable MARS by setting the value for MultipleActiveResultSets attribute to true in the connection string with the SqlClient.NET Data Provider as follows
string connectionString = "Data Source=MEDIACENTER;" +
 "Initial Catalog=AdventureWorks;Integrated Security=SSPI;" +
 "MultipleActiveResultSets=True";
  • Enable MARS using SQLSetConnectAttr function with the SQL Native Client ODBC driver. MARS feature also can be enabled using the Mars_Connection connection string key word. It accepts "yes" or "no" values; "no" is the default. MARS can be enabled by setting SQL_MARS_ENABLED_YES as follows
SQLSetConnectAttr(hdbc, SQL_COPT_SS_MARS_ENABLED,SQL_MARS_ENABLED_YES, SQL_IS_UINTEGER);
  • Enable MARS using the DBPROPSET_SQLSERVERDBINIT property with SQL Native Client OLE DB Provider as follows
// Set the MARS property.
DBPROP rgPropMARS;

// The following is necessary since MARS is off by default.
rgPropMARS.dwPropertyID = SSPROP_INIT_MARSCONNECTION;
rgPropMARS.dwOptions = DBPROPOPTIONS_REQUIRED;
rgPropMARS.dwStatus = DBPROPSTATUS_OK;
rgPropMARS.colid = DB_NULLID;

V_VT(&(rgPropMARS.vValue)) = VT_BOOL;
V_BOOL(&(rgPropMARS.vValue)) = VARIANT_TRUE; 

// Create the structure containing the properties.
DBPROPSET PropSet;

PropSet.rgProperties = &rgPropMARS;
PropSet.cProperties = 1;
PropSet.guidPropertySet = DBPROPSET_SQLSERVERDBINIT;

// Get an IDBProperties pointer and set the initialization properties.
pIDBProperties->SetProperties(1, &PropSet);

Note MARS allows interleaved execution of the following statements before completion. SELECT, FETCH, READTEXT, RECEIVE BULK INSERT (or bcp interface) and Asynchronous cursor population.

Problem Example

A .NET application which has to reduce the inventory of the item in the database when an item is sold. The application has to retrieve sales order detail from one table and then update the inventory in another table. If the application tries to open 2 result sets at a time, then it throws “connection busy” error.

This is achieved through two sequential connections to the database, one to read the amount of items sold and one to update the inventory with the reduced amount. However to support the proper set up of the queries for the second connection for deducting the correct amounts from the correct products second query requires results of the first query. And this is achieved using two arraylists. In case of a high-traffic web site, large amounts of memory would be needed to cache these ultimately throw-away values.

//Arraylists to hold product id & quantity
ArrayList ids = new ArrayList();
ArrayList qtys = new ArrayList();

//Connection string 
string connectionString = "Data Source=MEDIACENTER;Initial Catalog=AdventureWorks;Integrated Security=SSPI;";

//Retrieve sales order 
string strSQLGetOrder = "Select * from Sales.SalesOrderDetail WHERE SalesOrderID = 45672";

SqlConnection readConnection = new SqlConnection(connectionString);
readConnection.Open();

SqlCommand readCommand = new SqlCommand(strSQLGetOrder, readConnection);
using (SqlDataReader rdr = readCommand.ExecuteReader())
{
 //Read Id & quantity
}

//close the connection
readConnection.Close();

//Query for updating inventory         
string strSQLUpdateInv = "UPDATE Production.ProductInventory " +
 "SET Quantity=Quantity-@amt WHERE (ProductID=@pid)";
//New connection for updating          
SqlConnection writeConnection = new SqlConnection(connectionString);

writeConnection.Open();

//Create SQL command and set the data types for parameters

for(int lp=0;lp<ids.Count;lp++)
{
  //Set values for parameters and execute the query
  writeCommand.ExecuteNonQuery();
}
writeConnection.Close();

Solution Example

A .NET application which has to reduce the inventory of the item in the database when an item is sold. The application has to retrive sales order detail from one table and then update the inventory in another table. MARS is enabled, hence a single connection is used. This helps in reducing the overall connections to the database and also there is no need to have in-memory variables to storing the result of read. The MARS code is also shorter and therefore easier to read and maintain.

//Enable MARS
string connectionString = "Data Source=MEDIACENTER;" +
 "Initial Catalog=AdventureWorks;Integrated Security=SSPI;" +
"MultipleActiveResultSets=True";
//Query for retrieving sales order
string strSQLGetOrder = "Select * from Sales.SalesOrderDetail" + 
   "WHERE SalesOrderID = 45672";

//Query for updating inventory            
string strSQLUpdateInv = "UPDATE Production.ProductInventory " +
  "SET Quantity=Quantity-@amt WHERE (ProductID=@pid)";

//Create a single connection
SqlConnection marsConnection = new SqlConnection(connectionString);
marsConnection.Open();

SqlCommand readCommand = new SqlCommand(strSQLGetOrder, marsConnection);
SqlCommand writeCommand = new SqlCommand(strSQLUpdateInv, marsConnection);

//Set data types for parameters for update query and execute the read query
using (SqlDataReader rdr = readCommand.ExecuteReader())
{
 while (rdr.Read())
 {
           //Set values for parameters and execute update query  

writeCommand.ExecuteNonQuery();

 }
}
marsConnection.Close();

References


Explicitly Open Connection While Executing Data Adapter's Fill Method Multiple Times

Explicitly Open Database Connection before executing Data Adapter's Fill Method, if it is required to execute Fill method multiple times on the same connection.


Why

By design, the Fill method leaves the connection in the same state as it was supplied to it. If the connection supplied is not in Open State, the Fill method will internally Open the connection and Close it before returning. In case of multiple back-to-back execution of Fill method, it is desirable to avoid such internal multiple Open and Close calls. If the connection is opened before executing the Fill method, it will not Open and Close the connection internally, which results in better performance.

If the Fill method is to be executed only once, it is desirable not to Open the connection as it will be taken care internally.


When

If it is required to execute Fill method multiple times back-to-back on the same connection, it is desirable to explicitly Open the connection before executing the Fill method for better performance.


How

Open the Connection before executing the Fill method of Data Adapter. After executing multiple Fill methods, Close the connection:

...
using (SqlConnection connection = new SqlConnection("...")
{   
  connection.Open();
  ...
  adapter.Fill(dataset, "Employees");
  adapter.SelectCommand = sqlProd;
  adapter.Fill(dataset, "Department");  
  ...
  connection.Close();
}
...


Problem Example

A web based application for Employees Management, requires to display a set of combo-boxes for Country and Language selection in the Employee Entry User Interface. The application gets the list of Country and Language from the database. The following code Open and closes the multiple times in short span, this has adverse performance impact:

...
using (SqlConnection connection = new SqlConnection("...")
{
       SqlCommand sqlCountry = connection.CreateCommand();
       sqlCat.CommandText = "Select * from Country";
       SqlCommand sqlLanguage = connection.CreateCommand();
       sqlProd.CommandText = "Select * from Language";
       SqlDataAdapter adapter = new SqlDataAdapter();
       adapter.SelectCommand = sqlCountry;       
       DataSet dataset = new DataSet();
       adapter.Fill(dataset, "Country");
       adapter.SelectCommand = sqlLanguage;
       adapter.Fill(dataset, "Language");
}
... 


Solution Example

A web based application for Employees Management, requires to display a set of combo-boxes for Country and Language selection in the Employee Entry User Interface. The application gets the list of Country and Lanaguage from the databse. The following code opens the database connection explicitly this avoids the data adapter to open and close connection internally multiple times. Thus it has better performance. :

...
using (SqlConnection connection = new SqlConnection("...")
{
       SqlCommand sqlCountry = connection.CreateCommand();
       sqlCat.CommandText = "Select * from Country";
       SqlCommand sqlLanguage = connection.CreateCommand();
       sqlProd.CommandText = "Select * from Language";
       SqlDataAdapter adapter = new SqlDataAdapter();
       adapter.SelectCommand = sqlCountry;       
       DataSet dataset = new DataSet();
       connection.Open();        
       adapter.Fill(dataset, "Country");
       adapter.SelectCommand = sqlLanguage;
       adapter.Fill(dataset, "Language");
       connection.Close();
}
... 


References


Use ADO.NET Binary Serialization while Exchanging DataSet Over Network

Use DataSet Binary Serialization while exchanging DataSet over network, by setting the RemotingFormat property of the DataSet to SerializationFormat.Binary.


Why

The Binary Serialization of DataSet results in a smaller string size compared to the traditional XML serialization. It reduces the size of the content being sent over network, which is faster to transfer and improves performance.

Note In ADO.NET 1.1 DataSet Binary Serialization did not have any performance gain compared to XML serialization, because even after Binary Serialization, the resulting string was based on the XML data, which does not reduce the size of the resulting binary string.

When

If it is required to exchange huge DataSet over Network, use DataSet Binary Serialization to improve performance by reducing the size of the content.


How

In ADO.NET 2.0 DataSet and DataTable has a property called RemotingFormat. By default, the property is set to SerializationFormat.Xml, to preserve backward compatibility. Set the this property to SerializationFormat.Binary before serializing the DataSet:

...
DataSet dataSet = GetData(database_query);
dataSet.RemotingFormat = SerializationFormat.Binary;
BinaryFormatter binaryStr = new BinaryFormatter();
binaryStr.Serialize(stream,dataSet);
...


Problem Example

A .NET 2.0 Windows Forms based smart-client application for Oreder Management, calls a Web Service method to return Product Catalog. The Web Service calls the business logic method, which provides a DataSet of Product Catalog along with Price per Unit for each product. If the Product Catalog DataSet is huge, it will take a long time to transfer if it is shared across network. So, the DataSet is serialized as follows before sending it over network to reduce the content size and improve performance. But the binary serialization just flushes the XML content of DataSet into a binary content, it does not optimize the content itself. Hence, it results in a huge binary string for a Product Catalog, which impacts performance.

...
DataSet dataSet = GetProductCatalog();
BinaryFormatter binaryFormat = new BinaryFormatter();
MemoryStream memoryStream= new MemoryStream();
binaryFormat.Serialize(memoryStream, dataSet);
byte[] binaryArray = memoryStream.ToArray();
...

The following code illustrates how to deserialize the binary string back to a DataSet:

...
DataSet dataSet = new DataSet();
MemoryStream memoryStream = new MemoryStream(binaryArray);
memoryStream.Seek(0, 0);
BinaryFormatter binaryFormat = new BinaryFormatter();
dataSet = (DataSet) binaryFormat.Deserialize(memoryStream, null);
... 


Solution Example

A .NET 2.0 Windows Forms based smart-client application for Oreder Management, calls a Web Service method to return Product Catalog. The Web Service calls the business logic method, which provides a DataSet of Product Catalog along with Price per Unit for each product. If the Product Catalog DataSet is huge, it will take a long time to transfer if it is shared across network. So, the DataSet is serialized as follows before sending it over network to reduce the content size and improve performance. The DataSet RemotingFormat property, is set to SerializationFormat.Binary. The resulting binary stream is "true binary" and reduces the size significantly. The following code snippet illustrates how DataSet serialization:

...
DataSet dataSet = GetProductCatalog();
dataSet.RemotingFormat = SerializationFormat.Binary;
BinaryFormatter binaryFormat = new BinaryFormatter();
MemoryStream memoryStream= new MemoryStream();
binaryFormat.Serialize(memoryStream, dataSet);
byte[] binaryArray = memoryStream.ToArray();
...

The following code can be used to deserialize the binary string back to a DataSet:

...
DataSet dataSet = new DataSet();
dataSet.RemotingFormat = SerializationFormat.Binary;
MemoryStream memoryStream = new MemoryStream(binaryArray);
memoryStream.Seek(0, 0);
BinaryFormatter binaryFormat = new BinaryFormatter();
dataSet = (DataSet) binaryFormat.Deserialize(memoryStream, null);
... 


References


Specify CloseConnection For DataReaders To Close Associated Connection Along With The DataReader

Enable the CommandBehavior.CloseConnection enumeration property of DataReader in ExecuteReader method to ensure that the associated connection is closed when the DataReader is closed


Why

If connection is not closed along with the associated DataReader, then the connection is not returned to the connection pool until garbage collection occurs. This delays the reuse of the connection and negatively affects performance and scalability.

Setting CommandBehavior.CloseConnection enumeration for DataReader makes sure that the associated connection is closed when the DataReader is closed and is released to pool.


When

If a function is returning a DateReader to calling code, then use CommandBahavior.CloseConnection to ensure the connection gets closed when the reader is closed


How

During creating a DataReader object, specify the CommandBehavior.CloseConnection enumeration in call to ExecuteReader. Make sure that calling method is closing the reader.

  • Create DataReader object with CommandBehavior.CloseConnection enumeration
public SqlDataReader CustomerRead(int CustomerID)
{
 //... create connection and command, open connection  
 return myCommand.ExecuteReader(CommandBehavior.CloseConnection);
}
  • Read data in client code and close reader
//... client code
SqlDataReader myReader = CustomerRead(10248);
//... read some data
myReader.Close(); 
// reader and connection are closed


Problem Example

A web based application for Employees Management, requires retrieving employee related information from database while performing different functionalities across different modules. Therefore a generic class is implemented for retrieving customer information from database, which returns a DataReader object that can be used by calling module. The issue is that generic class does not have control over the calling module. Therefore calling module may or may not close the connection. This failure to close database connections could cause the application to run out of database connection impacting application performance and scalability. The following code illustrates the problem with the current application.

public SqlDataReader CustomerRead(int CustomerID)
{
    //... create connection and command, open connection
    return myCommand.ExecuteReader();
}

//... client code
SqlDataReader myReader = CustomerRead(10248);

//... read some data
myReader.Close(); // reader closed but connection is not closed 


Solution Example

A web based application for Employees Management, requires retrieving employee related information from database while performing different functionalities across different modules. Therefore a generic class is implemented for retrieving customer information from database, which creates DataReader object with CommandBehavior.CloseConnection property. When calling module uses DataReader and closes it, automatically associated connection gets closed and returns to the pool. The following code illustrates the solution example

public SqlDataReader CustomerRead(int CustomerID)
{
   //... create connection and command, open connection
   // Enable CloseConnection for DataReader
   return myCommand.ExecuteReader(CommandBehavior.CloseConnection);
}


//... client code
SqlDataReader myReader = CustomerRead(10248);

//... read some data
myReader.Close(); // reader and connection are closed 


References


Use SequentialAccess For Very Wide Rows Or For Rows With BLOBs

Enable the SequentialAccess property of DataReader using CommandBehavior.SequentialAccess enumeration for reading very wide rows or for rows with binary large object (BLOB) data to return specific bytes from the retrieved row instead of returning the entire row


Why

The default behavior of the DataReader is to read an entire row into memory. But returning the entire row in case of BLOB data may consume large amounts of memory and also there will be latency for loading the data. Enabling the CommandBehavior.SequentialAccess property of DataReader allows retrieving BLOB data is only when you reference it. Therefore it is possible to stream the data or to send the data in chunks from the column containing the BLOB by using the GetBytes, GetChars, or GetString methods. This helps to reduce the memory consumption and also reduces the latency in loading data

When

  • If it is required to read data from a wide row or a row with BLOB data from a database table, use the CommandBehavior.SequentialAccess enumeration for DataReader.
  • If it is possible to work with one piece of the data at a time, where don't need to work the whole object at once.
  • If it is required to access partial data from the BLOB or wide row.

Note

  • When CommandBehavior.SequentialAccess is used, data from columns must be retrieved in sequence. For example, if there are three columns, and the BLOB data is in the third column, first retrieve the data from the first and second columns, then the data from the third column
  • Behavior may depend on the data source. For example, returning a BLOB from Microsoft Access will load the entire BLOB being loaded into memory, rather than sequentially as it is received

How

Enable the CommandBehavior.SequentialAccess enumeration property for DataReader, and then sequentially retrieve data from columns. For example, if there are three columns and the BLOB data is in the third column, retrieving data from the third column before the first or second columns results that the values in first and second columns are no longer available. Use the GetBytes or GetChars methods of the DataReader to retrieve chunks of data. It is possible to specify a specific buffer size of data to be returned, and a starting location for the first byte or character to be read from the returned data. Pass a null array to GetBytes or GetChars method which will return the array with data. Following are key steps for the same

  • Allocate a buffer to hold a BLOB chunk
// Allocate a buffer to hold a BLOB chunk 
int bufferSize = 100;
byte[] outbyte = new byte[bufferSize];  
  • Set the SequentialAccess property to SqlDataReader
// Create connection and command. Open connection
...
...
SqlDataReader myReader = myCommand.ExecuteReader(CommandBehavior.SequentialAccess);
  • Read data from other columns sequentially
while (myReader.Read())
{
  // Get the first column before the BLOB using methods like GetString/GetIntX
  int data1 = myReader.GetInt32(0);  // First column
  ...
}

* Read data in bytes from BLOB column into allocated buffer

// Read the bytes into outbyte[] by specifying starting index and retain the number of bytes returned in retval
int retval = myReader.GetBytes(1, startIndex, outbyte, 0, bufferSize);


Problem Example

A web application which displays various books & publications available for online purchasing. This application has to retrieve various information like the publisher ID, book name, logo, etc from the database and display on a web page. Logo is an image, which is a BLOB, where all other fields are characters. Since the application has to retrieve BLOB data, there is latency in displaying the web page. Following is a code snippet for problem example

// Create connection and command. Open connection
...
...
SqlDataReader myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
  // Get data from other columns using methods like GetString/GetIntX
  string data1= myReader.GetInt32(0); // First column
  ...
  ... 
  // Read whole BLOB data.
  string blobdata = myReader.GetString(1);

 // Write data to a file or to a Web page.
 ...
} 


Solution Example

A web application which displays various books & publications available for online purchasing. This application has to retrieve various information like the publisher ID, book name, logo, etc from the database and display on a web page. Logo is an image, which is a BLOB, where all other fields are characters. SequentialAccess property is set to DataReader so that BLOB data can be retrieved in chunks as bytes and write to web page to avoid latency in displaying the screen as well as to reduce the memory consumption. Following is a sample code snippet.

// allocate a buffer to hold a BLOB chunk.
int bufferSize = 100;
byte[] outbyte = new byte[bufferSize];
// Create connection and command. Open connection
...
...
// Set the SequentialAccess property to SqlDataReader
SqlDataReader myReader = myCommand.ExecuteReader(CommandBehavior.SequentialAccess);

while (myReader.Read())
{
 // Get data sequentially
 // Read data from other columns using methods like GetString/GetIntX.
 string data1 = myReader.GetInt32(0); // First column
 ...
 ...
 // Read BLOB as bytes into outbyte[].
 int retval = myReader.GetBytes(1, startIndex, outbyte, 0, bufferSize);
 while (retval == bufferSize)
 {
    // Write data to a file or to a Web page.
    . . .
    startIndex += bufferSize;
    retval = myReader.GetBytes(1, startIndex, outbyte, 0, bufferSize);
 }
}


References


Use Asynchronous Commands for Parallel Non-Blocking Executions

Use Asynchronous Commands API such as BeginExecuteNonQuery, BeginExecuteReader and BeginExecuteXmlREader for parallel and non-blocking command execution scenarios.

Why

The asynchronous command execution does not block the thread till the command execution is finished. The Asynchronous Commands API BeginExecuteNonQuery, BeginExecuteReader and BeginExecuteXmlREader facilitate parallel execution of multiple database access commands, which provides better performance.

The ADO.Net 1.1 commands ExecuteNonQuery, ExecuteReader and ExecuteXmlReader are the synchronous commands. These synchronous commands will wait and block the current thread till the control returns back, which degrades performance where parallel execution is feasible.


When

The Asynchronous Commands should be used when it is feasible and required to execute database calls in parallel. For example, if there are two database servers from which we need to get the data, these two calls can be made in-parallel, which will save time for separate execution of each command.


How

Following are the steps for using the Asynchronous commands API.

  • Set the connection string appropriately. To use Asynchronous Commands API, the connections on which the commands will be executed must be initialized with async=true in the connection string. An exception will be thrown if any of the asynchronous methods are called on a command with a connection that doesn't have async=true in its connection string:
server=MyDatabaseServer;database=MyDatabase;integrated security=true;async=true
  • Create Connection and Command Objects and use BeginExecute* methods for asynchronous database call:
...
using(SqlConnection conn1 = new SqlConnection(dbConnStr1))
using(SqlConnection conn2 = new SqlConnection(dbConnStr2))
{
   // get employee info  
   conn1.Open();
   SqlCommand cmd1 = new SqlCommand("SELECT EmployeeID, FirtName, Name FROM Employee " 
   			+    "WHERE EmployeeID=@id", conn1);
   cmd1.Parameters.Add("@id", SqlDbType.Char, 5).Value = empId;
   IAsyncResult arEmployee = cmd1.BeginExecuteReader();
   // get addresses  
   c2.Open();  
  SqlCommand cmd2 = new SqlCommand("SELECT * FROM Addresses WHERE EmployeeID=@id", conn2);
  cmd2.Parameters.Add("@id", SqlDbType.Char, 5).Value = empId;
  IAsyncResult arAddresses = cmd2.BeginExecuteReader();
...
  • Use WaitHandle API to get a notification once the execution is finished:
WaitHandle[] handles = new WaitHandle[2];
handles[0] = arEmployee.AsyncWaitHandle;
handles[1] = arAddresses.AsyncWaitHandle;
int index = WaitHandle.WaitAny(handles, 5000, false); // 5 secs
...  
  • Once the WaitHandle notification is received, get the Reader Object by executing EndExecuteReader method, :
...
switch(index) 
{
  case 0: // employee query execution is finished
     r = cmd1.EndExecuteReader(arEmployee);
     //process reader
     r.Close();
  case 1: // addresses query is ready
     r = cmd2.EndExecuteReader(arOrders);
     //process reader
     r.Close();
}
...
  • Instead of using WaitHandle API, developer can also use a CallBack API as follows:
...
AsyncCallback callbackMethod = new AsyncCallback(CallbackMethod);
cmd1.BeginExecuteReader(callbackMethod, cmd1);
...
  • As soon as the asynchronous command execution is finished, it will execute the delegated Callback method. The Callback delegate can get the Data Reader and process it as follows:
...
private void CallbackMethod(IAsyncResult result) 
{    
   try 
   {        
      // get the command object back
      SqlCommand command = (SqlCommand)result.AsyncState;
      SqlDataReader dr = command.EndExecuteReader(result);
      // process reader    
   } 
   catch (Exception ex) 
   {        
      // Exception Handling    
   }
}
...


Problem Example

A .NET 2.0 web based application for Employees Payroll, facilitates to view the Pay Slips for a given month for a given employee. The application has to retrieve the Employee Information such as Employee Id, Employee Name and Employee Date of Joining from the employee database server. The application also has to retrieve the Pay Slip data from a Pay Slip database server. The application implements synchronous execution of both the commands, which takes more time and thus degrades the performance of the application.

...
using(SqlConnection conn1 = new SqlConnection(dbConnStr1))
using(SqlConnection conn2 = new SqlConnection(dbConnStr2))
{  
  // get employee info  
  conn1.Open();
  SqlCommand cmd1 = new SqlCommand("SELECT EmployeeID, FirtName, Name FROM Employee "
  		+    "WHERE EmployeeID=@id", conn1);
  cmd1.Parameters.Add("@id", SqlDbType.Char, 5).Value = empId;
  SqlDataReader r1 = cmd1.ExecuteReader();  
  //process reader
  r1.Close();
  // get pay slips  
  c2.Open();  
  SqlCommand cmd2 = new SqlCommand("SELECT * FROM PaySlips WHERE EmployeeID=@id", conn2);
  cmd2.Parameters.Add("@id", SqlDbType.Char, 5).Value = empId;  
  SqlDataReader r2 = cmd2.EndExecuteReader(arOrders);  
  //process reader  
  r2.Close();
}
...

Solution Example

A .NET 2.0 web based application for Employees Payroll, facilitates to view the Pay Slips for a given month for a given employee. The application has to retrieve the Employee Information such as Employee Id, Employee Name and Employee Date of Joining from the employee database server. The application also has to retrieve the Pay Slip data from a Pay Slip database server. The application uses Asynchronous Commands API to execute both the commands, which provides better performance by parallel execution:

...
using(SqlConnection conn1 = new SqlConnection(dbConnStr1))
using(SqlConnection conn2 = new SqlConnection(dbConnStr2))
{  
  // get employee info  
  conn1.Open();  
  SqlCommand cmd1 = new SqlCommand("SELECT EmployeeID, FirtName, Name FROM Employee " +
  	"WHERE EmployeeID=@id", conn1);  
  cmd1.Parameters.Add("@id", SqlDbType.Char, 5).Value = empId;  
  IAsyncResult arEmployee = cmd1.BeginExecuteReader();
  // get pay slips  
  c2.Open();  
  SqlCommand cmd2 = new SqlCommand("SELECT * FROM PaySlips WHERE EmployeeID=@id", conn2);
  cmd2.Parameters.Add("@id", SqlDbType.Char, 5).Value = empId;  
  IAsyncResult arPaySlips = cmd2.BeginExecuteReader();    
  // if any database call returns process it  
  WaitHandle[] handles = new WaitHandle[2];  
  handles[0] = arEmployee.AsyncWaitHandle;  
  handles[1] = arPaySlips.AsyncWaitHandle;  
  int index = WaitHandle.WaitAny(handles, 5000, false); // 5 secs
  
  switch(index) 
  {  
     case 0: 
       // employee query execution is finished  
       r = cmd1.EndExecuteReader(arEmployee);  
       //process reader   
       r.Close();
     case 1: 
       // addresses query is ready  
       r = cmd2.EndExecuteReader(arOrders);  
       //process reader  
       r.Close();  
  }
}
... 


References

Personal tools