[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 385
  • Last Modified:

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.
0
dselby5
Asked:
dselby5
2 Solutions
 
lluthienCommented:
well.. the batch update is doing the actual work in the database
so it'll be the slow..

what is ReturnModTask2 doing?
0
 
leonstrykerCommented:
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now