Measure Query Run-time

[darkhelmutis]

Is there a way to measure how long it takes a query to run in Access 2007? I have a macro that will do it, but it is somewhat buggy i.e. after it runs Access tends to lock up, not show the results, etc. Is there a better way to do this? Using a stopwatch isn't accurate enough, but I thought of it!!

Thanks,
DH


Here is a description of my method:

Basically, I set each commandbutton's onclick event to run an [event procedure] and the macro it should run in the Tag property. This command runs the getTime module I created and the macro name in the Tag is passed as an argument. I use DoCmd.RunMacro and the passed argument to run the intended macro:

Private Sub Command0_Click()
getTime (Command0.Tag)
End Sub

Sub getTime(ByVal strMacroName As String)
Dim startTime As Double
Dim endTime As Double

startTime = Time() 'get start time

DoCmd.RunMacro strMacroName, 1 'run the macro

endTime = Time() 'get end time

MsgBox (endTime - startTime) * 24 * 60 * 60 'calculate run time in seconds
End Sub

 

[raskew]

At the risk of being strung-up for pointing to another forum, take a look here http://www.accessmonster.com/Uwe/Fo...ba/31939/Query-duration-time#8c9bba8378871uwe and see if this provides any help.

Bob

 

[darkhelmutis]

Bob-

Thanks for the help. I will see if I can get this code to work more reliably than my current incarnation!

DH

 

[RivenSkaye]

For anyone landing here in the future, that link is now a pop-up and ad ridden gambling site and it has never been picked up for archival. This information is now lost forever.
@raskew you won't be strung up, but copying over at least the basic info or giving a summarizing TL;DR helps prevent loss of this kind of information in this way.

 

[The_Doc_Man]

Since the link was messed up, I'll post a short answer. I have found an easy way to time a query is to to have some SINGLE variables handy, using the Timer() function that returns time-of-day as milliseconds since midnight.

Single1 = Timer()
run the query using vba launch methods
Single2 = Timer()
Elapsed = (Single2 - Single1) * 1000

The "Elapsed" variable now contains the execution duration of the query in seconds and fractions thereof, with a resolution of about 1/60th of a second. Using the TIME() function is less precise because that function returns times rounded to the nearest second. Some queries ain't that slow.

Other methods exist that are more sensitive but require more extensive setup because of the issues involved with using the high-precision timer that is used by some network drivers.

 

[MajP]

@isladogs has extensive information and tools for timing queries

I Need Advice on Optimizing a Large Database Query

Hey guys.....🥰 I am currently working on a project where I need to optimize a large SQL query that’s becoming quite slow. The database has millions of records, and the query involves several JOINs and subqueries. I am relatively new to advanced SQL optimization techniques and would greatly...

www.access-programmers.co.uk