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.