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
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.
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
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.
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.
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.