Fast Code Sample

[Guus2005]

While looking for fast code i found this one sitting in a forgotten directory on my NAS.
It has a different way of addressing a field in a recordset.

I don't know where i got it from but here it is:

tblStatus had 85K records in the sample database.
DoitSlow took 1000msec
DoitFast took 200msec

The sample database is attached.

Share & Enjoy!

Public Function DoitSlow()

    Dim strStaStatus As Variant
    Dim lngConID     As Long
    Dim lngStaID     As Long
    Dim rst          As DAO.Recordset
    
    Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblStatus", dbOpenDynaset)

    rst.MoveFirst
    Do Until rst.EOF
        strStaStatus = rst("staStatus")
        lngConID = rst("ConID")
        lngStaID = rst("staID")
        rst.MoveNext
    Loop
    rst.Close
    Set rst = Nothing

End Function
Public Function DoitFast()

    Dim strStaStatus As Variant
    Dim lngConID     As Long
    Dim lngStaID     As Long
    Dim rst          As DAO.Recordset
    Dim fld1         As DAO.Field
    Dim fld2         As DAO.Field
    Dim fld3         As DAO.Field
    
    Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblStatus", dbOpenDynaset)
    Set fld1 = rst("staStatus")
    Set fld2 = rst("ConID")
    Set fld3 = rst("staID")
    
    rst.MoveFirst
    Do Until rst.EOF
        strStaStatus = fld1
        lngConID = fld2
        lngStaID = fld3
        rst.MoveNext
    Loop
    rst.Close
    Set rst = Nothing

End Function

 

[Ranman256]

1. dont loop thru recordsets. slowest.
2. run queries. update or other

 

[NauticalGent]

That is pretty amazing...wonder what the reason is for the performance boost?

 

[Josef P.]

That is pretty amazing...wonder what the reason is for the performance boost?

With rst("staStatus") (= rst.Fields.Item("staStatus")) you have to search for the data field in the field collection each time. In the example with Field-Reference this is necessary only 1x at the beginning.
I assume that the duration of

Set rst = CurrentDb.OpenRecordset("SELECT staStatus, ConID, staID FROM tblStatus", dbOpenDynaset)
  ' see/test dbOpenDynaset vs dbOpenForwardOnly

'rst.MoveFirst ' <--- why?
Do Until rst.EOF
        strStaStatus = rst.Fields(0).Value
        lngConID = rst.Fields(1).Value
        lngStaID = rst.Fields(2).Value
        rst.MoveNext
loop

will be between the field reference and the field name example.

 

[Pat Hartman]

Did anybody check that it worked?

 

[NauticalGent]

I recently bought the 2002 Access Desktop Developers Handbook after a couple of AWF Heavy Hitters recommended it. Chapter 15 is dedicated to Application Optimization. Towards the end of the chapter they list 24 optimization tips and show benchmark data to support their claim.

Tip #2: Cache object references. And although they used ADO instead of DAO, their code example is doing something very similar with a record set. The result: 40% faster

The summary is "Dots in object references will always slow down your code. You should take whatever effort you can to reduce the number of redundant dots...."

Very interesting.

 

[Guus2005]

.Fields("staStatus") is slow
.Fields(0) is slightly faster
and apparently
set fld = .Fields("staStatus") is very fast.

I just reduced a process from 2 hours to 2 minutes.

It is a gamechanger!

 

[SHANEMAC51]

DoitSlow взял 1000msec
DoitFast взял 200msec

I compared reading and writing to 85,000 records
for reading 40-45 percent were very impressed
with the record gave mostly 10-15 percent , not enough

'~~~~reading from a table~~~~85000 rec
Slow: 899 msec
Fast: 468 msec
'~~~ 52.0578420467186 %
'~~~ 56.1676646706587 %
'~~~ 58.5485854858549 %
'~~~ 57.6701268742791 %
'~~~ writing to a table~~~~ 4141 / 4852 = 85.3462489694971 %
'~~~ writing to a table~~~~ 4195 / 4586 = 91.4740514609682 %
'~~~ writing to a table~~~~ 4398 / 5274 = 83.3902161547213 %
'~~~ writing to a table~~~~ 4164 / 4586 = 90.7980811164413 %

 

[The_Doc_Man]

I'm going to be Captain Obvious here and suggest that it is the repeated expressed or implied lookups.

Using .Fields("staStatus") you have to find the Fields collection for the recordset (each time) and THEN do a name lookup.

Using .Fields(0) you have to find the fields collection but using a numeric index doesn't involve a name search, so SHOULD be faster.

Using set fld = .Fields("staStatus"), you do the lookup once and retain a pointer to the actual object. So you bypass the fields collection. You have the address in that object, which in VBA is a euphemism for "pointer."

What this proves is that, as is typical for emulation environments, they don't retain frequently referenced items in a cache. Which means examining the recordset to find the .Fields collection EVERY ITERATION OF THE LOOP. (And then for the first case, finding a specifically named object.) It is not clear because MS doesn't expose their code, but I wouldn't be surprised to find that named collections don't have a name lookup index, unlike tables with text keys.

 

[arnelgp]

using ADODB.Recordset, is almost as fast:

'arnelgp
Public Function AlmostAsFaster()

    Dim strStaStatus As Variant
    Dim lngConID     As Long
    Dim lngStaID     As Long
    Dim oRows As Variant
    Dim i As Long, j As Long
    Dim rst As Object
    Dim con As Object
    
    Set con = CreateObject("Adodb.Connection")
    Set rst = CreateObject("Adodb.Recordset")
    
    con.Open CurrentProject.AccessConnection
    rst.Open "SELECT * FROM tblStatus", con
    oRows = rst.GetRows
    For i = 0 To UBound(oRows, 2) - 1
        'For j = 0 To UBound(oRows, 1) - 1
        'Next j
        lngStaID = oRows(0, i)
        lngConID = oRows(1, i)
        strStaStatus = oRows(2, i)
        
    Next i
    rst.Close
    con.Close

End Function

 

[ebs17]

I just reduced a process from 2 hours to 2 minutes.

Congratulations. But surely something more took place than just the aforementioned change in field access.

It is to be expected that this advantage comes proportionally less to the effect if then the loop is not simply run through, but if with the data something is made, which is for its part also expenditure and costs time.

I was allowed to accompany a process in a forum, which was reduced from a runtime of about 24 hours to a few minutes.