 | |
How to measure query performance
comp.databases.ms-access comp.databases.ms-access
 |
| | Thread Tools | Display Modes |
#11
 
| |||
| |||
|
Re: How to measure query performance -
02-08-2011
, 08:37 PM
|
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. |
slower by looking at the query plan, assuming they were equivalent,
as opposed to completely different operations.
And if the difference between the two is slight (under a second) you
have to ask yourself when such a different could possibly matter. In
most cases, small differences are only meaningful if the query is
being executed in a loop, and in a lot of cases, the fact that
you're looping is an indication that you might consider trying to
rearchitect what you're doing so it can be done with set operations
instead of in a loop.
In short, I have a hard time imagining a scenario when all the work
you describe to do these tests would be worthwhile at all.
--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/
#12
| |||
| |||
|
|
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? |
think it's the DBEngine.Idle commands, but there I only see a cache
refresh action, no flush. So maybe it's not possible -- somebody
else chime in here?
--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/
#13
| |||
| |||
|
|
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? |
generic, non-native interface to do so?
--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/
#14
| |||
| |||
|
|
Yitzak <terrysha... (AT) yahoo (DOT) co.uk> wrote innews:7dd5bc54-74c5-424b-807e-d8e73104334f (AT) p12g2000vbo (DOT) googlegroups.co m: 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? Not with ADO. You can do it with DAO, but I'd have to look it up. I think it's the DBEngine.Idle commands, but there I only see a cache refresh action, no flush. So maybe it's not possible -- somebody else chime in here? -- David W. Fenton * * * * * * * * *http://www.dfenton.com/ contact via website only * *http://www.dfenton.com/DFA/ |
testing rel. speeds using DAO.
I want to find rel speeds, absolute speeds are not important to me.
Just want these queries which are executed often to run as quickly as
possible.
When trying to speed them up, they got slower in some cases.
How can I see query plans? That would be helpfull.
Thanks
#15
| |||
| |||
|
|
On Feb 9, 2:39*am, "David-W-Fenton" <NoEm... (AT) SeeSignature (DOT) invalid wrote: Yitzak <terrysha... (AT) yahoo (DOT) co.uk> wrote innews:7dd5bc54-74c5-424b-807e-d8e 73104334f (AT) p12g2000vbo (DOT) googlegroups.co m: 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? Not with ADO. You can do it with DAO, but I'd have to look it up. I think it's the DBEngine.Idle commands, but there I only see a cache refresh action, no flush. So maybe it's not possible -- somebody else chime in here? The end use program uses ADODB lib to talk to access. I have no prob testing rel. speeds using DAO. I want to find rel speeds, absolute speeds are not important to me. Just want these queries which are executed often to run as quickly as possible. |
Wouldn't SHOWPLAN give you what you need?
Quote:
|
When trying to speed them up, they got slower in some cases. How can I see query plans? That would be helpfull. |
does optimize subqueries, SHOWPLAN doesn't give you that information
(because it was never implemented), so if you're trying to test SQL
with subqueries, you won't necessarily be able to tell what's going
on.
--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/
#16
| |||
| |||
|
|
Yitzak <terrysha... (AT) yahoo (DOT) co.uk> wrote innews:6579a5ee-075b-4184-a730-a92f5aad1576 (AT) m27g2000prj (DOT) googlegroups.co m: On Feb 9, 2:39*am, "David-W-Fenton" <NoEm... (AT) SeeSignature (DOT) invalid wrote: Yitzak <terrysha... (AT) yahoo (DOT) co.uk> wrote innews:7dd5bc54-74c5-424b-807e-d8e 731043... (AT) p12g2000vbo (DOT) googlegroups.co m: 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? Not with ADO. You can do it with DAO, but I'd have to look it up. I think it's the DBEngine.Idle commands, but there I only see a cache refresh action, no flush. So maybe it's not possible -- somebody else chime in here? The end use program uses ADODB lib to talk to access. I have no prob testing rel. speeds using DAO. I want to find rel speeds, absolute speeds are not important to me. Just want these queries which are executed often to run as quickly as possible. What kind of queries are you comparing that you need benchmarking? Wouldn't SHOWPLAN give you what you need? When trying to speed them up, they got slower in some cases. How can I see query plans? That would be helpfull. Google JET SHOWPLAN. Keep in mind that while the query optimizer does optimize subqueries, SHOWPLAN doesn't give you that information (because it was never implemented), so if you're trying to test SQL with subqueries, you won't necessarily be able to tell what's going on. -- David W. Fenton * * * * * * * * *http://www.dfenton.com/ contact via website only * *http://www.dfenton.com/DFA/ |
My Queries are Selects across multiple tables to produce identical
results.
One version SEEMS quicker, hence why I wanted a command to clear
cache. I've got it seemingly quicker by trial and error.
Getting a showplan will hopefully get me to understand why ones
faster than the other, if it is really faster.
The real test is real life app but I'm a while away from releasing it.
I guess Its going take me a while to understand JET SHOWPLAN if its
anyhting like SQL Server's showplan.
It be really neat if we had
dbcc drop cleanbuffers
dbcc drop proccache
as you do in MS SQL Server.
Thanks for your help. I'm no expert with access and I keep forgetting
that its JET that does the work..
#17
| |||
| |||
|
|
I guess Its going take me a while to understand JET SHOWPLAN if its anyhting like SQL Server's showplan. |
though.
--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.