Speed tests and speed comparisons for everyone (1 Viewer)

[ebs17]

The attached access file contains two tables, three queries, a module and a stopwatch class. If these elements are transferred to your own application, you will be able to carry out speed tests and speed comparisons for a large number of tasks without great effort. This solution is not as broad and graphically expanded as Colin Riddington's tests, but can be adapted very quickly for self-help.
The QueryPerformanceCounter is used for the measurements, which offers higher precision than methods that are derived from the system clock. This also makes it easier to evaluate individual executions of queries.
The simple code: The queries to be checked (alternatively sub-procedures for VBA tests) and a description are stored in an array. The sAddition variable can be used to document variants of measurements, e.g. changed indexing for the same queries. The cOne flag can be used to control whether a one-off measurement is carried out, because you hardly want to run anything in a loop for several minutes.

Sub start_Test()
    Dim QSU As New QpcStoppuhr
    Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    Dim rs As DAO.Recordset
    Dim rsE As DAO.Recordset

    Dim S As Variant
    Dim sAddition As String
    Dim i As Long, j As Long

    Const cRun As Long = 6
    Const cOne As Boolean = True          ' for True: one-time run to estimate the running time

    ' Pairs: QueryName, TestName
    S = Array("qryTestG", "Indexed Order By", _
              "qryTestH", "Where vs Having", _
              "qryTestI", "Indexed Filter fields", _
              "qryTestJ", "First vs GroupBy", _
              "qryStacked2", "Stacked Queries", _
              "qryTestL", "Subquery 1", _
              "qryTestM", "Subquery 2")

    Set db = CurrentDb
    If cOne Then
        Set rsE = db.OpenRecordset("SELECT Test, Addition, Duration FROM tblLog_One", dbOpenDynaset)
        With rsE
            sAddition = "singly"
            For i = 0 To UBound(S) Step 2
                Set qd = db.QueryDefs(S(i))
                QSU.StartT
                Set rs = qd.OpenRecordset(dbOpenSnapshot)
                rs.MoveLast
                QSU.StopT
                .AddNew
                .Fields("Test") = Format(i \ 2 + 1, "00_") & S(i + 1)
                .Fields("Addition") = sAddition
                .Fields("Duration") = QSU.TimeString
                .Update
                rs.Close
                qd.Close
            Next
            .Close
        End With
        DoCmd.OpenTable "tblLog_One"
    Else
        Set rsE = db.OpenRecordset("SELECT Run, Test, Addition, Duration FROM tblLog", dbOpenDynaset)
        With rsE
            sAddition = "normal"
            For j = 0 To cRun
                For i = 0 To UBound(S) Step 2
                    Set qd = db.QueryDefs(S(i))
                    QSU.StartT
                    Set rs = qd.OpenRecordset(dbOpenSnapshot)
                    rs.MoveLast
                    QSU.StopT
                    If j > 0 Then                     ' skip the starting lap
                        .AddNew
                        .Fields("Run") = j
                        .Fields("Test") = Format(i \ 2 + 1, "00_") & S(i + 1)
                        .Fields("Addition") = sAddition
                        .Fields("Duration") = QSU.time
                        .Update
                        rs.Close
                        qd.Close
                    End If
                Next
            Next
            .Close
        End With
        DoCmd.OpenQuery "qCTLog"
    End If
    MsgBox "Test completed"
End Sub

Here in the presentation, elements from Query runs slow with NOT IN were used for code and images of the results.

Single run to estimate the running time:

Repeated runs to refine the results:

Have fun applying.

 

[isladogs]

Thanks for uploading this and for providing a link to my speed test contents page.
I'll have a play with your version in the near future.

Several years ago, I ran a series of tests to compare 6 different methods of timing

Timer Comparison Tests

This article compares 6 different timing methods with a view to determining the reliablility and consistency of each method.

www.isladogs.co.uk

These included the high precision timer you have used in your code.
Whilst it does have a precision of less than one microsecond, I'm not convinced that level of precision is appropriate
There are also reports that it is less accurate than some of the system clock methods.
After using it for a while, I went back to other methods such as Timer & timeGetTime/

Nevertheless, whichever method of timing is used, it is unlikely that it will affect the relative performance measurements in any tests

One small thing. The API declaration in your class module is 32-bit only.
This minor alteration will allow it to run in VBA7 (2010 onwards both 32 or 64-bit) or VBA6 (A2007 or earlier)

'Win-API
#If VBA7 Then
    Private Declare PtrSafe Function QueryPerformanceCounter Lib "kernel32" _
                            (X As Currency) As Boolean
    Private Declare PtrSafe Function QueryPerformanceFrequency Lib "kernel32" _
                            (X As Currency) As Boolean
#Else
    Private Declare Function QueryPerformanceCounter Lib "kernel32" _
                            (X As Currency) As Boolean
    Private Declare Function QueryPerformanceFrequency Lib "kernel32" _
                            (X As Currency) As Boolean
#End If

Having said that, both APIs return Long rather than Boolean values in my code

'High resolution timer
  'Both API Functions accept a LARGE_INTEGER (64-bit) as Arguments which VBA does not support.
  'VBA does have the Currency Data Type which is 64-bit and can substitute for LARGE_INTEGER
 
#If VBA7 Then
     'Retrieves the Frequency of the Performance Counter (Fixed)
    Declare PtrSafe Function getFrequency Lib "kernel32" Alias "QueryPerformanceFrequency" (ByRef Frequency As Currency) As Long
    
    'Will retrieve the current value of the Windows Performance Counter, a High Resolution Time
    'Stamp used for Time Interval Measurements which can go down to the microsecond level (1/1000000)
    Declare PtrSafe Function getTime Lib "kernel32" Alias "QueryPerformanceCounter" (ByRef Counter As Currency) As Long

#Else
     'Retrieves the Frequency of the Performance Counter (Fixed)
    Declare Function getFrequency Lib "kernel32" Alias "QueryPerformanceFrequency" (ByRef Frequency As Currency) As Long
    
    'Will retrieve the current value of the Windows Performance Counter, a High Resolution Time
    'Stamp used for Time Interval Measurements which can go down to the microsecond level (1/1000000)
    Declare Function getTime Lib "kernel32" Alias "QueryPerformanceCounter" (ByRef Counter As Currency) As Long
#End If

I've just checked the APIs in Ron de Bruin's Win API Converter utility and that shows Long also