CODEWITHSUNDEEP
ms-access
James
James

Reputation: 1766

How can I determine the query execution time in ms access 2007?

I'd like to determine the effect that changes to my queries are having. To do this, I need some performance metric. Is it possible to determine the execution time for a query in MS Access? Using external programs, or changing the registry (SHOWJETPLAN) are not an option as my workstation is really locked down by the network admins... so I need an in-Access solution. Thanks!

Upvotes: 1

Views: 7827

Answers (1)

mwolfe02
mwolfe02

Reputation: 24227

I have a quick and dirty approach that I use for evaluating relative performance of alternative algorithms, be they different functions, queries, etc.

I make use of the Run command of the Access.Application object. It allows me to quickly compare 2, 3, 4, etc., different approaches. For each additional approach, I just create another Function named ThingX.

I then use the number of iterations to work around GetTickCount's limitation of roughly 10 ms accuracy. If you want even finer resolution than GetTickCount can provide, you can use a high-resolution timer like QueryPerformanceCounter. Personally, I don't think that is worth all the extra work. If you are writing performance critical code in VBA and are worried about shaving microseconds off your execution time, you're using the wrong tool (VBA) for the job.

Private Declare Function GetTickCount Lib "kernel32" () As Long

Sub TimeThings() 'vv
Const NumThings = 2
Const Iterations = 2500
Dim t As Integer, i As Integer, s As Long

    For t = 1 To NumThings
        s = GetTickCount
        For i = 1 To Iterations
            'Debug.Print Run("Thing" & t, i)
            Run "Thing" & t, i
        Next i
        Debug.Print "Thing "; t, GetTickCount - s; " ms elapsed"
    Next t
End Sub

Function Thing1(Optional Val)
    Dim i
    For i = 1 To Val
        Thing1 = Thing1 & Chr(65 + (i Mod 57))
    Next i
End Function

Function Thing2(Optional Val)
    Dim i
    Thing2 = Space(Val)
    For i = 1 To Val
        Mid(Thing2, i) = Chr(65 + (i Mod 57))
    Next i
End Function

On my system, running TimeThings() yields the following output:

Thing  1       4087  ms elapsed
Thing  2       2652  ms elapsed

Upvotes: 1

Related Questions