Clocking a query

[Anhjan]

Hey Gang,

Is there a way to clock how long a query runs? I have to decide which of the two queries I have built would run faster. And I think by seeing how fast a it takes can help decide which would be the most effecient.

Thanks,
Jan

 

[raskew]

Don't know where I got the API portion of the following. Hope someone will take credit, since it sure seems to work.

Copy / paste the following code to a new module.

To time a query, from the debug window type:

? querytimer("myQueryName")

'*******************************************

Private Declare Function a2Ku_apigettime Lib "winmm.dll" _
Alias "timeGetTime" () As Long
Dim lngstartingtime As Long

Sub a2kuStartClock()
    lngstartingtime = a2Ku_apigettime()
End Sub
Function a2kuEndClock()
    a2kuEndClock = a2Ku_apigettime() - lngstartingtime
End Function


Function QueryTimer(strQueryName As String)

Dim db As DATABASE
Dim qry As QueryDef
Dim rs As Recordset
Dim msg As String

Set db = CurrentDb
Set qry = db.QueryDefs(strQueryName)

'Start the clock
a2kuStartClock
Set rs = qry.OpenRecordset()

'Stop the clock and print the results to the debug window
msg = strQueryName & " executed in: " & a2kuEndClock & " milliseconds"
'Debug.Print strQueryName & " executed in: " & a2kuEndClock & _
'" milliseconds"
MsgBox msg, vbInformation + vbOKOnly, "Query Time For: " & StrConv(strQueryName, 3)
rs.Close
db.Close
Set db = Nothing

End Function