Hi

How can I measure query performance. I can see in the DB tools section
of Access there is an Analyse Perfromace which is useful.

However I want to run Query1 see how long it takes then run Query2
and see how long it takes both queries produce the same columns and
rows. But I THINK due to caching the second query is always going to
benefit.


Does anyone have simple tips. The only thing I can think of is to shut
access down and restart after each test.

How do others do this.

Thanks

Try adding a module like below and running it several times:

Option Compare Database
Option Explicit

Private Declare Function timeGetTime _
Lib "winmm.dll" () As Long
Private mlngStartTime As Long

Private Function ElapsedTime() As Long
ElapsedTime = timeGetTime() - mlngStartTime
End Function

Private Sub StartTime()
mlngStartTime = timeGetTime()
End Sub

Public Function MyTest()

Call StartTime
DoCmd.OpenQuery "Query1"
DoCmd.GoToRecord acDataQuery, "Query1", acLast

Debug.Print ElapsedTime() & _
": Using Query1."

Call StartTime
DoCmd.OpenQuery "Query2"
DoCmd.GoToRecord acDataQuery, "Query2", acLast

Debug.Print ElapsedTime() & _
": Using Query2."
End Function

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access
Co-author: "Access Solutions", published by Wiley

"Yitzak" <terryshamir (AT) yahoo (DOT) co.uk> wrote

On Feb 6, 10:57*pm, "Arvin Meyer" <arv... (AT) invalid (DOT) org> wrote:
Thanks Arvin

I'm doing something v.similar now but using an API function I found on
web
Public Declare Function GetTickCount Lib "kernel32" () As Long.

Just wanted to check my improvements are real and not a function of
Access/Jet being clever 2nd time around.

Yitzak wrote:

Be very careful before leaping to any conclusions. If the
two queries use the same table, all the data will probably
be loaded into memory and the second time it's used, may be
dramatically faster only because of Windows optimizations.
The least you should do is restart windows between testing
each query. Even then, Windows may be working on a
different set of others tasks so you should also get a
statistical average across several test runs.

--
Marsh

On Feb 7, 5:19*pm, Marshall Barton <marshbar... (AT) wowway (DOT) com> wrote:
Thats the sort of thing I was worried about.

I'm not restarting windows - but repeat each query multiple of times.

"Marshall Barton" <marshbarton (AT) wowway (DOT) com> wrote


Wouldn't throwing away the results from the first pair of tests, then
running several more to develop an average minimize the effect of
windows' caching of data?

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

"Clif McIrvin" <clare.nomail (AT) gmail (DOT) com> wrote in
news:iipaju$94m$1 (AT) news (DOT) eternal-september.org:

I'd think much more important would be Jet's caching, and you can
control that by flushing the DBEngine cache between runs of the
query. Sure, it's possible disk caching could have an effect as
well, but what are you really testing? Jet or Windows? I'd say run
the first one, throw it out (because of Windows disk caching), flush
the Jet/ACE DBEngine cache and then run it again several times
(flushing the Jet/ACE cache between each run). That would give you
results for Jet that would probably keep most of Windows out of the
picture. Slowdowns from page faults and swapping to disk and all
that are just not something you can control for, so you'd want to
run the tests with the minimal programs running. That might include
turning off things like AV checking and the like, and any number of
background tasks.

But how precise a result do you really want? I'd think that running
several instances of the test in the same operating environment
would give you relative performance numbers, which are all that
matters (i.e., how much faster is one query than a different one?).

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

To eliminate the disk caching you can turn off (at least temporarily)
the windows cache. Dont worry about page faults, these are actually
normal behavior and not actually a fault - it is an instance when the
required chunk of memory is not in executable ram the processor can
get to so has to move things around. Minimizing these is beyond your
or anyones control and is really only a measure of memory movement
activity in and out of the cpu. Killing the AV temporarily is a
definite must. Likewise, things like windows update, messenger etc....
all the unnecessary background junk you dont actually need.

Statistically, if you run a 'clean' environment for your trials, and
say run the trials in two sets of tests, the first being say 100 runs
of the first query with the cache being flushed between each run,
followed by the second query being run the same way, the second set of
runs being the reverse but after you have restarted windows and set up
(at least as close as possible) the same environment (killing the av
and other background processes you dont need, turning off windows
virtual memory(cache)). Thats about as close as you'll get. One more
thing, which you are already doing but needs stating anyway, is run
the tests from code and record the results - just swap the order of
queries between the two runs.

Statistically thats about as fair a test as you can make for this
requirement. I am not sure what statistics you want to run at the end
of all this to compare them, but a simple t-test for differences
between the runtimes would be a good start, as well as some standard
descriptive statistics. Even numbers that might look like a big deal
dont necessarily mean a statistically valid difference between two
sets of results. Obviously if one query takes 1 second to run for each
iteration of the test and the other takes a minute for each iteration
you have a significant difference, but often times the numbers are
much closer and sometimes even backwards to what you think.

Cheers

The Frog

On Feb 8, 4:07*am, "David-W-Fenton" <dfas... (AT) dfenton (DOT) com> wrote:
Hi David

How do I clear down JET's cache?

I'm use ADO to run anmd return results - is there a command I can use
between calling Queries?

Clif McIrvin wrote:


Sure, but it ignores whatever scenario the query will be
used in the real app. If the query is used before the table
gets involved with other things, then the time spent loading
from disk should be measured. For example, a missing index
on a large table may cause a table scan that could be very
slow. The second time the query runs, the table scan could
be run in ram and be orders of magnitude faster.

Or, if the real app uses the query repeatedly without
involving a lot of other stuff that causes the table caching
to be flushed, the cache load time can be ignored as you
said.

--
Marsh