Section 5.7. Batch DataAdapter Commands for Better Performance | Visual Basic 2005: A Developers Notebook

5.7. Batch DataAdapter Commands for Better Performance

Many databases are able to execute commands in batches, reducing the total number of calls you need to make. For example, if you submit 10 update commands in a single batch, your code only needs to make 1 trip to the server (instead of 10). Cutting down the number of round-trips can increase performance, particularly on networks that have a high degree of latency. In .NET 2.0, the SqlDataAdapter is enhanced to use batching for updating, inserting, and deleting records.

Note: If you need an easy way to optimize DataSet updates, ADO. NET 's new batching can help you out.

5.7.1. How do I do that?

In previous versions of .NET, you could batch direct commands by concatenating them in a single string, and separating each with a semicolon. This syntax requires support from the database provider, but it works perfectly well with SQL Server. Here's an example that inserts two rows into a table:

Dim TwoInserts As String ="INSERT INTO Shippers" &_   "(CompanyName, Phone) VALUES "ACME", "212-111-1111;" & _   "INSERT INTO Shippers (CompanyName, Phone)" &_   VALUES "Grey Matter", "416-123-4567"      Dim cmd As New SqlCommand(TwoInsert) cmd.ExecuteNonQuery( )

As useful as this feature is, previous versions of .NET didn't provide any way to batch commands to one of the most important ADO.NET provider objectsthe data adapter. The data-adapter object scans a DataSet, and executes insert, delete, and update commands whenever it finds a new, removed, or changed row. Each of these commands is executed separately, which means that if your DataSet contains three new rows, the data adapter will make three round-trips to the server.

Note: It makes good sense to have batching support in the data adapter, because the data adapter is often used to commit more than one modification at a time.

.NET 2.0 improves the picture with a new SqlDataAdapter.UpdateBatchSize property. By default, the value of this property is set to 1, which causes each insert, update, or delete command to be executed separately. If you set the UpdateBatchSize to a larger number, the data adapter will group its commands into batches.

Example 5-7 is a console application, BatchedDataAdapterTest, that puts this technique to the test. BatchedDataAdapterTest retrieves data from the Orders table in the Northwind database and then makes changes to each row. To make life interesting, the module applies this update not once, but twiceonce without batching, and once with batch sizes set to 15. BatchedDataAdapterTest displays connection statistics for each approach, allowing you to compare their performance.

Example 5-7. Updates with and without batching

Imports System.Data.SqlClient      Module BatchedDataAdapterTest          Private ConnectString As String = _       "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI"     Private con As New SqlConnection(ConnectString)          Public Sub Main( )         ' Turn on statistics collection.         con.StatisticsEnabled = True              Dim Query As String = "SELECT * FROM Orders"         Dim cmd As New SqlCommand(Query, con)         Dim Adapter As New SqlDataAdapter(cmd)         Dim CommandBuilder As New SqlCommandBuilder(Adapter)                  Dim ds As New DataSet         con.Open( )         Adapter.Fill(ds, "Orders")         con.Close( )              ' Perform an update without batching.         ChangeRows(ds)         con.ResetStatistics( )         Adapter.Update(ds, "Orders")         Console.WriteLine("Statistics without batching....")         DisplayStatistics( )              ' Perform an update with batching (15 row batches).         ChangeRows(ds)         con.ResetStatistics( )         Adapter.UpdateBatchSize = 15         ' When performing a batch update you must explicitly         con.Open( )         Adapter.Update(ds, "Orders")         con.Close( )              Console.WriteLine("Statistics with batching....")         DisplayStatistics( )     End Sub          Public Sub ChangeRows(ByVal ds As DataSet)         For Each Row As DataRow In ds.Tables("Orders").Rows             Row("ShippedDate") = DateTime.Now         Next     End Sub          Public Sub DisplayStatistics( )         ' Retrive the hasthable with statistics.         Dim Stats As Hashtable = con.RetrieveStatistics( )              ' Display all the statistics.         For Each Key As String In Stats.Keys             Console.WriteLine(Key & " = " & Stats(Key))         Next         Console.WriteLine( )     End Sub      End Module

When you run this application, the rows will be updated, and a list of statistics will appear. Take a close look at these statistics, paying special attention to the number of round-trips made to the database, the total connection time, and the amount of data required to complete the updates. Here's a portion of the output generated by one run of the application that highlights some of the more important numbers:

Statistics without batching.... ConnectionTime = 5682 UnpreparedExecs = 831 ServerRoundtrips = 831 BytesSent = 2637094      Statistics with batching.... ConnectionTime = 6319 UnpreparedExecs = 56 ServerRoundtrips = 56 BytesSent = 1668160

This listing reports that, in the batched update, 831 rows were updated in 56 batches of 15 commands each. As you can see, batching reduced the amount of data that needed to be sent (by packing it more effectively into batches), which is one of the most important metrics of database scalability. On the other hand, the overall performance of the application hardly changed at all, and the connection time even increased slightly. Clearly, to make a meaningful decision about whether to use batching, you need to profile your application in a real-world scenario.

5.7.2. What about...

...the quirks and limitations of batched updates? Currently, only the SqlDataAdapter supports batching, although other providers may implement this functionality in the future. The actual implementation details will differ for each providerin the case of the SqlDataAdapter, the provider uses the sp_executesql system stored procedure to execute the batch. As for quirks, you'll notice a change to how the RowUpdated and RowUpdating events of the SqlDataAdapter work. When batching is enabled, these events fire once for every batch, not once for every row. That means that when the RowUpdated event fires, you can determine the number of rows affected, but not the row-by-row details of the changes made. This loss of information can make it more difficult to handle errors that occur somewhere inside a batch.

The ideal batch size depends on a variety of low-level factors, including the network architecture and the size of the rows. The best advice is to test your application with different batch settings. If you want all updates to be done in a single batch of unlimited size, set the UpdateBatchSize property to 0.