October 4, 2018 at 5:50 am
I always try different versions of queries and check the statistics and execution before deciding on which version to use. Occasionally I run into the situation where a query is listed as being better, e.g. QueryA = 75% QueryB = 25%, but QueryA actually runs in a shorter amount of time. What should I use to determine the best query?
If the statistics IO tells me that the faster query reads more records from tables, and I know the query will run very often, I will sometimes go with the query that reads less records since it will reduce IO in the long run. If the query is specific, for example a report query where perceived performance is important, I'll use the faster query.
Any thoughts? Thanks
ST
October 5, 2018 at 3:21 am
What I sometimes do, is run a query and time it.
Then rerun the query and time it.
Clear the cache, then rerun the query and time is.
And sometimes use some variations with clearing the plan cache as wel.
There might be huge differences between queries where you have cleared the cache and not have cleared the cache. And there might be differences between a first run (with a warm cache), a second run (with a hot cache) and the cleared run (with a cold cache).
This might give you an indication of what to do. But there are problems with this scenario. There often are caches (diskcache or SAN-caches) which also have an impact on the timing.
Also statistics might give an indication what is preferred. A problem which remains is that a typical 'warm' cache on a production machine is very different from a 'warm' cache on a testing system. So you might get an indication but this does not give conclusive results.
Ben
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply