Image of Navigational Map linked to Home / Contents / Search Batching Updates

by Stephan Grieger - Independent Developer
Image of Line Break

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.

Written by: Stephan Grieger
April '98

Image of Arrow linked to Previous Article Image of Arrow linked to Next Article
Image of Line Break