Search code examples
sqlvbams-access

In MS Access VBA, how to measure the time to display a query?

I want to measure the time that it takes to run and display a query. It appears that there are three different lengths of time:

A. The time between VBA opening the query and control returning to the next VBA line. This can be measured by:

dTime = Evaluate("Now()")
DoCmd.OpenQuery (sQueryName)
MsgBox (((Evaluate("Now()") - dTime) * 86400) & " seconds")

But this is not the whole time that it takes to calculate the datasheet. After the message box appears from the above code, for a large query, the datasheet window may still be mostly empty, waiting for its contents to be drawn.

B. The time between opening the query and when the datasheet is fully drawn.

Even this appears to not be the whole time to complete work on the query because, for a large query, when the datasheet is fully drawn, it still does not respond to a mouse click to select it.

C. The time between opening the query and its datasheet responding to a mouse click to select it.

In a query that I'm currently working on with 300,000 rows, the times are approximately:

  • A. 0.1 seconds
  • B. 9 seconds
  • C. 17 seconds

The time A is as reported in the message box, and so is exact. But B and C are counted manually by me watching the behavior of the datasheet window. I don't know if there is a way to programmatically detect the completion of B and C so that I could have those times accurately.

Even C is not the time to calculate all the rows of the query. At the end of C, scrolling the datasheet window can take another long time for the sheet to be redrawn. On my current query of 300,000 rows, this is about another 9 seconds, after which the datasheet is fully redrawn and responds to a mouse click to select it. I suppose this delay for scrolling means that each time, only the visible part of the datasheet is fully calculated.

I want to measure the speed-effect of a change in the code of the query, so it would be helpful to have an accurate measure of B and/or C. Is there a way to get that?

Solution

  • Use Timer, it is close enough:

    Dim StartTime As Double

StartTime = Timer
DoCmd.OpenQuery (sQueryName)
DoEvents
MsgBox CStr(Timer - StartTime) & " seconds"