ADO Batch update - very slow

Hi,

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))
                   
                   rsql.MoveNext
                   x = x + 1
                   lblRecordeCount.Caption = x
                   DoEvents
        Loop
        rsql.UpdateBatch

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?

Thanks,

David.
dselby5Asked:
Who is Participating?
 
lluthienConnect With a Mentor Commented:
well.. the batch update is doing the actual work in the database
so it'll be the slow..

what is ReturnModTask2 doing?
0
 
leonstrykerConnect With a Mentor Commented:
You are likely to get a better performance by using a SQL UPDATE statement and executing it on the Connection object, but it does depend on that ReturnModTask2  is doing.



0
 
dselby5Author Commented:
It turn out that in this case there was no primary key.  After the primay key was applied the process speed was increase 4000 fold.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.