VB6 Ms Access Database Editing large amount of records

Question

I need to process hundreds of thousands of records with VB6 and an MS Access database. I iterate through the recordset and edit each record. However it takes a lot of time to do so. Creating a database with the same amount of records using the Addnew and Update methods works much faster.

I will greatly appreciate if anyone shows me any code sample or just a strategy.

Here is the code

Data1(1).RecordSource = "Select * from TABLE order by Field_A ASC"
Data1(1).Refresh
If Data1(1).Recordset.RecordCount > 0 Then
    Data1(1).Recordset.MoveFirst
    Do
        Data1(1).Recordset.Edit
        Data1(1).Recordset.Fields("FIELD") = Sort_Value
        Data1(1).Recordset.Update
        Data1(1).Recordset.MoveNext
    Loop Until Data1(1).Recordset.EOF = True
End If

It is really quite quite simple. The real thing is, I forgot to mention, that tha HDD of the computer constantly reds/writes. This is actually the problem. With such a heavy load there is no way not to affect performance.

I first thought that the recordset generated by the query, keep in mind that we hane 1-2 Million records, causes this problem. I guess it resides on some temporary place on the hard drive and on in RAM. And so executin .Edit and .Update might be a problem in first positioning the cursor at the right place and then writing.

Don't know for sure. Probably there would be an expert to show me a way out.

Btw. I also tried with replacing the Loop Until Data1(1).Recordset.EOF = True statement with a fixed length cycle, because I also read, that this checking for Recordset.EOF also slows down performance.

Thank you in advance!

Answer 1

I created a table called test with the fields n and f(n)

Timed 3 different update subroutines - recordset without transaction - recordset with transaction - update query

Sub updateFunction_noTrans()
    Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset("test")
    rs.MoveFirst
    Do Until rs.EOF
        rs.Edit
        rs("f(n)") = rs("n") + 1
        rs.Update
        rs.MoveNext
    Loop
End Sub

This is basically what you are doing, a straight recordset while editing a field

Sub updateFunction_yesTrans()
    Dim i As Long
    Dim commitSize As Long
    Dim rs As Recordset
    commitSize = 5000
    Set rs = CurrentDb.OpenRecordset("test")
    DBEngine.Workspaces(0).BeginTrans
    rs.MoveFirst
    Do Until rs.EOF
        rs.Edit
        rs("f(n)") = rs("n") + 1
        rs.Update
        rs.MoveNext
        i = i + 1
        If i = commitSize Then
            DBEngine.Workspaces(0).CommitTrans
            DBEngine.Workspaces(0).BeginTrans
            i = 0
        End If
    Loop
    DBEngine.Workspaces(0).CommitTrans
End Sub

This is the same idea but with transactions. I commit 5000 records at a time as there was some limit established around 9k-10k per commit. You can edit this I believe by going into the registry.

Sub updateFunction_updateQuery()
    CurrentDb.Execute ("UPDATE test SET test.[f(n)] = [n]+1;")
End Sub

This is faster than any of the recordset methods. E.g. on around 2 million records it took ~20 seconds without transactions, ~18-19 seconds with transactions, ~14 seconds with the update query.

This all is under the assumption that the field to be updated depends on values calculated from other fiends within that records

To really speed up these kind of actions, sometimes it is dependent on the situation and more detail is needed if this doesn't apply.

Edit: Used old core 2 duo machine + no indices on the fields