Batching Updates
by Stephan Grieger - Independent Developer
Now, this is not a trick for the seasoned SQL Server developer - I'm sure they have their mysterious ways to do all of this server side. But, for the rest of us, who use VB and just want to get the job done…
Most of us deal primarily in database applications where the data is entered by a user during the course of their normal day-to-day activities. But what if the data came in the form of a text file from an outside source. Say also that the text file contained up to 4.5 million records, which needed to be imported overnight ready for the operators in the morning. You have a time window of two and a bit hours for your application to run because other processes also need to be run after yours has finished. What do you do?
Obviously reading in each record and saving it to the database is not going to work for you. You would be lucky if you got half way through the text file before you ran out of time. So what else?
Strangely enough, the answer can actually be found in the Visual Basic help file though some trial and error is required to get it working.
The secret actually lies in batching :- where you batch a set of records for update or add together. This means that the server will basically update say 1,000 records all at once, rather than 1 at a time. The speed increase you get on this is actually outstanding.
First we will need to dim a few variables.
Dim wrkODBC As Workspace Dim dbBatch As Connection
Next we need to create a workspace and connect to the database. In the example MyDatabase refers to the database in SQL Server and TheDatabase refers to the ODBC connection name.
Set wrkODBC = CreateWorkspace("ODBCWorkspace", "Sa", "", dbUseODBC)
wrkODBC.DefaultCursorDriver = dbUseClientBatchCursor
Set dbBatch = wrkODBC.OpenConnection("MyDatabase", dbDriverNoPrompt, 0,_
"ODBC;DATABASE=MyDatabase; UID=Sa; PWD=;DSN = TheDatabase;LOGINTIMEOUT=10")
Now we create a recordset on the table we are going to be writing the data to.
Set dynUpdate = dbBatch.OpenRecordset("Select * From TheTable",_
dbOpenDynaset, 0, dbOptimisticBatch)
From now on we simply do all our adds as per normal. ie: using the AddNew and Update methods provided by Visual Basic.
Once you have made all the updates, or say 1000, then you need to run the batch update command. dynUpdate.Update dbUpdateBatch
Ok, so it's not brain surgery, but what the heck… it works for me. And it works fast enough.
|