ADO Batch update - very slow
Posted on 2005-04-29
I have a process that is written in VB that enters a calculated record into a feild in table is SQL Server. There are currently 222,872 records in the table.
The code that performs this operation is:
Set rsql = New Recordset
rsql.Open sSQL, CN, adOpenDynamic, adLockBatchOptimistic
Do Until rsql.EOF
rsql!task2 = (ReturnModTask2(rsql!TaskNum))
x = x + 1
lblRecordeCount.Caption = x
The code makes it through the loop quite quickly but then seems to stall on the rsql.UpdateBatch.
Can you offer any suggestions to do this more efficiently?