Recordcount performance testing (1 Viewer)

GBalcom

Much to learn!
Local time
Today, 05:49
Joined
Jun 7, 2012
Messages
459
For years now I've avoided using the native Dcount function like the plague because I've been told it has terrible performance.

Today, I'm starting to plan a new application (rewrite of an existing one), so I've been looking into performance. I have a table that I need to regularly query the record count on. Currently, it's up to about 48,000 records but will grow significantly over the lifetime of this new application.

So, I tested 4 different avenues, with the cls timer function available here: https://modthemachine.typepad.com/my_weblog/2010/06/how-to-time-an-operation.html

options were:
  • Allen Brownes Ecount Function
  • Native Dcount Function
  • DAO Recordset
  • a saved SQL Pass thru query




Here were the results:
? RunTests
Using Allen Browne ECount Function: 0.069454;RecCount = 48062
Using DCount NativeFunction: 0.067723;RecCount = 48062
Using DAO.Recordset: 0.223418;RecCount = 48062
Using SQL PassThru Query: 0.064766;RecCount = 48062

The results are in seconds, and 48,062 records were pulled.

I'm surprised to see that Dcount worked pretty well. I've been using DAO.Recordset instead for many things, and it turns out it's significantly slower.

I'm going to post the code in case someone can improve on it or point out an error. I'm also curious if anyone is willing to do a similar test on a larger recordset.

Here is the clsTimer:

Code:
'@Folder("VBAProject.Classes")
Option Compare Database
Option Explicit

Private Declare Function QueryPerformanceFrequency _
            Lib "kernel32" (lpFrequency As Currency) As Long
Private Declare Function QueryPerformanceCounter _
            Lib "kernel32" (lpPerformanceCount As Currency) As Long

Private ConversionFactor As Currency
Private CurrentStartTime As Currency


Public Sub Start()
    Dim iReturn As Long
    iReturn = QueryPerformanceCounter(CurrentStartTime)
End Sub


Private Sub Class_Initialize()
    Dim iReturn As Long
    iReturn = QueryPerformanceFrequency(ConversionFactor)
End Sub


Public Property Get CurrentTime() As Double
    Dim NewTime As Currency
    Dim iReturn As Long
    iReturn = QueryPerformanceCounter(NewTime)
    Dim TotalTime As Currency
    TotalTime = NewTime - CurrentStartTime
    CurrentTime = TotalTime / ConversionFactor
End Property

And the module for the tests

Code:
Option Compare Database
Option Explicit

Public Function usingECount() As String
    ' Status:  In Devlopment
    ' Comments:
    ' Params  :
    ' Returns : Boolean
    ' Created : 04/16/19 15:05 GB
    ' Modified:
    
    'TVCodeTools ErrorEnablerStart
    On Error GoTo PROC_ERR
    'TVCodeTools ErrorEnablerEnd

    Dim t As New clsTimer
    Dim i As Long
    
    t.Start
    i = ECount("[WoNbr]", "DBA_WO")
    
    usingECount = "Using Allen Browne ECount Function: " & Format(t.CurrentTime, "0.000000") & ";" & "RecCount = " & i
 
    'TVCodeTools ErrorHandlerStart
PROC_EXIT:
    Set t = Nothing
    Exit Function

PROC_ERR:
    MsgBox Err.Description, vbCritical, "mWoCountOptions.usingECount"
    Resume PROC_EXIT
    Resume

    Resume
    'TVCodeTools ErrorHandlerEnd

End Function


Public Function usingDCount() As String
    ' Status:  In Devlopment
    ' Comments:
    ' Params  :
    ' Returns : Boolean
    ' Created : 04/16/19 15:05 GB
    ' Modified:
    
    'TVCodeTools ErrorEnablerStart
    On Error GoTo PROC_ERR
    'TVCodeTools ErrorEnablerEnd

    Dim t As New clsTimer
    Dim i As Long
    
    t.Start
    i = DCount("[WoNbr]", "DBA_WO")
    
    usingDCount = "Using DCount NativeFunction: " & Format(t.CurrentTime, "0.000000") & ";" & "RecCount = " & i
 
    'TVCodeTools ErrorHandlerStart
PROC_EXIT:
    Set t = Nothing
    Exit Function

PROC_ERR:
    MsgBox Err.Description, vbCritical, "mWoCountOptions.usingDCount"
    Resume PROC_EXIT
    Resume

    Resume
    'TVCodeTools ErrorHandlerEnd

End Function


Public Function usingRecordset() As String
    ' Status:  In Devlopment
    ' Comments:
    ' Params  :
    ' Returns : String
    ' Created : 04/16/19 15:11 GB
    ' Modified:
    
    'TVCodeTools ErrorEnablerStart
    On Error GoTo PROC_ERR
    'TVCodeTools ErrorEnablerEnd


    Dim t As New clsTimer
    Dim i As Long
    
    t.Start
    
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSql As String

    Set dbs = CurrentDb
    strSql = "SELECT [WoNbr] " & _
             "FROM [DBA_WO]"
    Set rst = dbs.OpenRecordset(strSql, dbOpenDynaset, dbSeeChanges)

    With rst
        .MoveLast
        i = .RecordCount
        .Close
    End With

    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing

    usingRecordset = "Using DAO.Recordset: " & Format(t.CurrentTime, "0.000000") & ";" & "RecCount = " & i

    'TVCodeTools ErrorHandlerStart
PROC_EXIT:
    Set t = Nothing
    Exit Function

PROC_ERR:
    MsgBox Err.Description, vbCritical, "mWoCountOptions.usingRecordset"
    Resume PROC_EXIT
    Resume

    Resume
    'TVCodeTools ErrorHandlerEnd

End Function

Public Function usingPassThruQry() As String
    ' Status:  In Devlopment
    ' Comments:
    ' Params  :
    ' Returns : String
    ' Created : 04/16/19 15:18 GB
    ' Modified:
    
    'TVCodeTools ErrorEnablerStart
    On Error GoTo PROC_ERR
    'TVCodeTools ErrorEnablerEnd

    Dim t As New clsTimer
    Dim i As Long
    Dim rst As DAO.Recordset
    
    t.Start

    Set rst = CurrentDb.QueryDefs("qryWoCountPassThru").OpenRecordset
    
    
    
    'With rst
     '   .MoveLast
      '  If .RecordCount = 1 Then
        
            i = rst.Fields("Count()").Value
       ' End If
        
    'End With


    usingPassThruQry = "Using SQL PassThru Query: " & Format(t.CurrentTime, "0.000000") & ";" & "RecCount = " & i
    'TVCodeTools ErrorHandlerStart
PROC_EXIT:
    Set rst = Nothing
    Set t = Nothing
    Exit Function

PROC_ERR:
    MsgBox Err.Description, vbCritical, "mWoCountOptions.usingPassThruQry"
    Resume PROC_EXIT
    Resume

    Resume
    'TVCodeTools ErrorHandlerEnd

End Function

Public Function RunTests() As Boolean
    ' Status:  In Devlopment
    ' Comments:
    ' Params  :
    ' Returns : Boolean
    ' Created : 04/16/19 15:21 GB
    ' Modified:
    
    'TVCodeTools ErrorEnablerStart
    On Error GoTo PROC_ERR
    'TVCodeTools ErrorEnablerEnd

Debug.Print usingECount
Debug.Print usingDCount
Debug.Print usingRecordset
Debug.Print usingPassThruQry

RunTests = True

    'TVCodeTools ErrorHandlerStart
PROC_EXIT:
    Exit Function

PROC_ERR:
    MsgBox Err.Description, vbCritical, "mWoCountOptions.RunTests"
    Resume PROC_EXIT
    Resume

    Resume
    'TVCodeTools ErrorHandlerEnd

End Function


If one doesn't have it, here's the Ecount function off of Allen Browne's site, found here: http://www.allenbrowne.com/ser-66.html


Code:
Public Function ECount(Expr As String, Domain As String, Optional Criteria As String, Optional bCountDistinct As Boolean) As Variant
On Error GoTo Err_Handler
    'Purpose:   Enhanced DCount() function, with the ability to count distinct.
    'Return:    Number of records. Null on error.
    'Arguments: Expr           = name of the field to count. Use square brackets if the name contains a space.
    '           Domain         = name of the table or query.
    '           Criteria       = any restrictions. Can omit.
    '           bCountDistinct = True to return the number of distinct values in the field. Omit for normal count.
    'Notes:     Nulls are excluded (whether distinct count or not.)
    '           Use "*" for Expr if you want to count the nulls too.
    '           You cannot use "*" if bCountDistinct is True.
    'Examples:  Number of customers who have a region: ECount("Region", "Customers")
    '           Number of customers who have no region: ECount("*", "Customers", "Region Is Null")
    '           Number of distinct regions: ECount("Region", "Customers", ,True)
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSql As String

    'Initialize to return Null on error.
    ECount = Null
    Set db = DBEngine(0)(0)

    If bCountDistinct Then
        'Count distinct values.
        If Expr <> "*" Then             'Cannot count distinct with the wildcard.
            strSql = "SELECT " & Expr & " FROM " & Domain & " WHERE (" & Expr & " Is Not Null)"
            If Criteria <> vbNullString Then
                strSql = strSql & " AND (" & Criteria & ")"
            End If
            strSql = strSql & " GROUP BY " & Expr & ";"
            Set rs = db.OpenRecordset(strSql)
            If rs.RecordCount > 0& Then
                rs.MoveLast
            End If
            ECount = rs.RecordCount     'Return the number of distinct records.
            rs.Close
        End If
    Else
        'Normal count.
        strSql = "SELECT Count(" & Expr & ") AS TheCount FROM " & Domain
        If Criteria <> vbNullString Then
            strSql = strSql & " WHERE " & Criteria
        End If
        Set rs = db.OpenRecordset(strSql)
        If rs.RecordCount > 0& Then
            ECount = rs!TheCount        'Return the count.
        End If
        rs.Close
    End If

Exit_Handler:
    Set rs = Nothing
    Set db = Nothing
    Exit Function

Err_Handler:
    MsgBox Err.Description, vbExclamation, "ECount Error " & Err.Number
    Resume Exit_Handler
End Function
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 12:49
Joined
Jan 14, 2017
Messages
18,186
Have a look at my series of speed comparison tests which include different methods of counting for both indexed & unindexed fields
http://www.mendipdatasystems.co.uk/speed-comparison-tests-7/4594524997

The results may surprise you.
Whatever approach you use, make sure you index any fields you wish to run counts on.
The benefits will become increasingly obvious the the dataset expands well above what you have now.
I tested up to 10 million records or so

I've also done several other tests that may be of interest and can be found in the same area of my website.
For example I also compared the accuracy of different methods of timing.
http://www.mendipdatasystems.co.uk/timer-comparison-tests/4594552971
The high resolution timer is good but I think no better than the simpler timeGetTime approach. There are suggestions that the hi-res timer, though it has greater precision, may be less accurate
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 05:49
Joined
Aug 30, 2003
Messages
36,118
The recordset method is hobbled by having to return all records over the network, which should always be avoided if possible. I'd do something like:

Code:
    strSql = "SELECT Count(*) As HowMany " & _
             "FROM [DBA_WO]"

And just

i = rst(0)
 

sonic8

AWF VIP
Local time
Today, 13:49
Joined
Oct 27, 2015
Messages
998
I'm surprised to see that Dcount worked pretty well. I've been using DAO.Recordset instead for many things, and it turns out it's significantly slower.
The ECount function is the DAO.Recordset approach. I wonder what you did differently to achieve significantly slower results with a DAO.Recordset.


The native DCount had serious issues in Access 95 and A97. Not only performance issue but also all sorts of memory/resource leaks. However, it has been improved in later releases, particularly with the change from JET- to ACE-Engine. - So, I'm not surprised that there is hardly any difference between DCount and ECount any more.


On the other hand, I missed how many iterations you were doing to get the results. The issues with native DCount in the past worsened significantly with number of calls to the function. Like the first 10k were fine, the next 10k were slow and then the real memory/ressource issues (errors) started to show. (10k is a arbitrary number inserted here, I cannot remember any actual numbers)
 

isladogs

MVP / VIP
Local time
Today, 12:49
Joined
Jan 14, 2017
Messages
18,186
My tests indicated that DCount is slow for non-indexed fields.
However for indexed fields it is slightly faster than other methods...though the differences are usually negligible.
See the article I linked in my previous reply for both a discussion of the results and the test database used
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:49
Joined
Feb 19, 2002
Messages
42,981
@GBalcom
The prohibition against domain functions is WITHIN queries, NOT on forms or in code (except inside loops).

When you use a domain function in a query it runs once for EVERY SINGLE ROW the query returns. So, if the query returns 48,000 records, then the function executes 48,000 times. THAT is the problem. One-offs are no better or worse then other methods. many people also mistakenly use lookups rather than opening a recordset when they need a bunch of fields from a single record. Why use 10 dLookup()s when you can retrieve a single row with a single query and all 10 columns are retrieved.

So - do NOT use domain functions In:
1. queries
2. code loops

Otherwise, they are fine.
 

Users who are viewing this thread

Top Bottom