Visual Basic 1002005 [A Developers Notebook] [Electronic resources] نسخه متنی

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.