Query duration time

S

SJW_OST

Hi,

I have code that is accessing a very large database via ODBC connection. It
is slow due to the size of the database being accessed and works fine but I
want to know how long it takes my query to gather the data. How would I write
code to time the duration it takes for the query to run and then display the
time duration in a message box?

Any help is greatly appreciated.
Stephen
 
R

raskew via AccessMonster.com

Hi -

Give this a try:

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

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
Debug.Print strQueryName & " executed in: " & a2kuEndClock & _
" milliseconds"

rs.Close
db.Close
Set db = Nothing

End Function


HTH - Bob
 
M

Marshall Barton

SJW_OST said:
I have code that is accessing a very large database via ODBC connection. It
is slow due to the size of the database being accessed and works fine but I
want to know how long it takes my query to gather the data. How would I write
code to time the duration it takes for the query to run and then display the
time duration in a message box?
Click to expand...


That's fine if you want to know the time to get the first
record.

OTOH, if you expect to measure the retrieval time for the
entire recordset, then add a the line:
rs.MoveLast
after the Set rs line. The MoveLast forces Access to
retrieve all the records immediately.

OTOOH, it might(?) not matter because Access usually runs
the part of the query beyond what is needed immediately (the
first record in a recordset, the first screen's worth of
records for a query's datasheet or ...) as an asynchronous
task. And measuring the time for an asynchronous task is
nearly meaningless.
 
S

SJW_OST

I tried using this code in every way I know how and could not get it to work.
I kept getting Halt and RunTime errors. I thank you for the suggestion and I
appologize because I asked my question on the wrong operator. I meant to ask
how to time a macro from start to finish. I have made a new posting for this
question.

Again I appologize but I thank you very much for the help!.
 
G

GeoffG

Stephen:

Copy all the code below to a new module.
It contains some functions and subprocedures you can
call before and after running your query.
I'm assuming you can run your query from code and make
the appropriate calls to the following module before
and after running your query.


Option Explicit

' The timeGetTime function returns the number of milliseconds
' that have elapsed since since the current Windows session
' started. The timeGetTime function is fast, accurate and
' doesn't "roll over". Therefore, the function is suitable
' for measuring elapsed times. The timeGetTime function
' is in the Windows system file "winmm.dll", hence the
' following declaration, which allows VBA to access the
' function:
Private Declare Function timeGetTime Lib "winmm.dll" () As Long

Private mlngStartTime As Long

Public Function GetElapsedTime(fStart As Boolean) As Long

' This function can be called by another procedure to get
' the time when that procedure started and stopped,
' thus providing the elapsed time, ie the time it took
' for the other procedure to run.

Static lngStartMillisecond As Long

If fStart Then
lngStartMillisecond = timeGetTime()
Else
GetElapsedTime = timeGetTime() - lngStartMillisecond
End If

End Function


' ALTERNATIVE METHOD:

Public Sub StartTimer()
mlngStartTime = 0
' Start timer storing value in global lngStartTime:
mlngStartTime = timeGetTime()
End Sub

Public Function StopTimer() As Long
' Stop timer and return elapsed time in milliseconds:
StopTimer = timeGetTime() - mlngStartTime
End Function


' WAIT:

Public Sub WaitAWhile(lngMillisecondsToWait As Long)

Dim lngStart As Long
Dim lngEnd As Long

lngStart = timeGetTime()
lngEnd = lngStart

Do Until Abs(lngEnd - lngStart) > Abs(lngMillisecondsToWait)
DoEvents
lngEnd = timeGetTime()
Loop

End Sub



Regards
Geoff





message
news:[email protected]...
 
Top