One Thing at a (Hundred) Times

How do you know whether one piece of code performs better than another piece of logically equivalent code?  One approach is to spend six months of your life learning about algorithms and Big O notation.  But, chances are, if you are writing applications in Microsoft Access, that's overkill.  

Your bottlenecks are almost always going to be on the data access side of things.  The solution to most Access slowdowns is likely to be some combination of better indexing, returning fewer records from the backend data source, or loading records only when you need them.

Occasionally, you may have some function that you call inside a loop that you want to be as efficient as possible.  For example, it may be a complex function that runs against hundreds of thousands of records while looping through a recordset.

For instances like those, I have a simple performance profiling trick I use.  I simply create two or more functions named Thing1(), Thing2(), etc.  I then use the Access Application.Run() method to loop through each approach to see which one is fastest.  Finally, I use the GetTickCount() Windows API call to track timing with millisecond precision.

Source Code

Here is a contrived example showcasing a few different ways to modify a string:

Sub TimeThings()
    Const NumThings As Byte = 3
    Const Iterations As Long = 200000  'Start with a small number
    Dim t As Integer, i As Long, StartTicks(1 To NumThings) As Long

    For t = 1 To NumThings
        StartTicks(t) = GetTickCount  'GetTickCount() is a Windows API call
        For i = 1 To Iterations
            Debug.Assert Run("Thing" & t, "The dog jumps") = "The fox jumps"
        Next i
        Debug.Print "Thing "; t, GetTickCount - StartTicks(t); " ms elapsed"
    Next t
End Sub

Function Thing1(Optional Val)
    Dim s As String
    s = Val
    
    ' Replace "The dog jumps" with "The fox jumps"
    Mid(s, 5, 3) = "fox"
    
    Thing1 = s
End Function

Function Thing2(Optional Val)
    Dim s As String
    s = Val
    
    ' Replace "The dog jumps" with "The fox jumps"
    s = Left(s, 4) & "fox" & Right(s, 6)
    
    Thing2 = s
End Function

Function Thing3(Optional Val)
    Dim s As String
    s = Val
    
    ' Replace "The dog jumps" with "The fox jumps"
    s = Replace(s, "dog", "fox")
    
    Thing3 = s
End Function

Sample Output

Here is my output from running the above timing examples:

'--== Immediate Window ==--
TimeThings
Thing  1       7985  ms elapsed
Thing  2       8031  ms elapsed
Thing  3       8219  ms elapsed

TimeThings
Thing  1       7953  ms elapsed
Thing  2       8000  ms elapsed
Thing  3       8234  ms elapsed

I needed to use a very large number of iterations (200,000) to find a meaningful difference between my three (admittedly simple) functions.  You should always start with a very low number of iterations and adjust it upwards as needed, otherwise you risk hanging your machine if one of the processes takes longer than you expect.

Repeatability

You should always run the TimeThings() procedure at least twice, to ensure your results are repeatable.  After all, depending on the nature of your test, there are lots of other operations outside of Access that could impact the results.  For example, if a virus scan is running during only one of the three functions could skew that function and make it appear inefficient.  If you re-run the timing suite and get similar results, it suggests that there are in fact valid performance differences between the approaches.

Premature optimization

As a final note, I should point out that programmers tend to spend too much time saving time.  Additionally, they have a tendency to favor cleverness over readability when writing code.  But look again at the three sample functions.

If we were to choose an approach based on performance alone, it would be the least readable code by far.  All three of the lines below replace "The dog jumps" with "The fox jumps".  Which one most clearly expresses the intent of the function:

Mid(s, 5, 3) = "fox"                     'Thing1: fastest

s = Left(s, 4) & "fox" & Right(s, 6)     'Thing2: middle

s = Replace(s, "dog", "fox")             'Thing3: slowest

So, sure, you can shave a few microseconds off your code with Thing1 and show off your knowledge of the obscure Mid() statement.  But if the point of the function is to replace "dog" with "fox", then I'm going with the code that basically reads like that in plain English.

Image by congerdesign from Pixabay