April 29, 2003 at 12:50 am
Hi,
This is more a informative thread than a problem.
I've recently been trying to improve the performance of my stored procs - one was taking *gasp* all of 7 seconds! I thought I knew a reasonable amount about indexing and performance tuning, but I have to say there is always more to learn.
I came across a performance tips page that suggested specifying your index in your query to get the most out of it. Obviously there are people out there who know this, but for everyone else, this can be a godsend. I'm ecstatic to report that my 7 second proc now runs at 1 second. I've just made a huge performance gain, from a single line of code.
The change? It's suggested to always have a clustered index on each table (which I had), but after checking my execution plan, it was using the clustered index, instead of another index I had specifically made for it!
You can specify the index after you declare the table you're selecting from, and boom - instant performance gains.
ie. Select * from tablename [index=myspecialindex]
where etc etc...
One thing I did find a bit of a pain when performance testing queries was the fact that the first time running would always take longer than subsequent times. Is this to do with re-compiling or buffering the results from last time?
anyway, hope this is useful to people out there.
April 29, 2003 at 5:40 am
Sometimes it will work - adding the hint that is. Downside is that if the index gets changed, query plan may not be as good. If the index gets dropped, proc doesn't work so good maybe!
First time you run it you're compiling the query plan and caching the data, subsequent calls should always be faster. You can compare apples to apples by either clearing the cache each time, or just always using the 2nd run time for comparison (which I find to be easier).
Andy
May 1, 2003 at 10:30 am
I personally dont like to add the index hint to queries. What happens when the tables have grown the data has changed, the applications have evolved. A well meaning hint could end up being your worst query.
IMHO it is better to investigate why the optimizer preferred the clustered index to the index that you felt was more suited.
If you are tuning "select" queries the best metric to look for is not the "time" taken which can vary depending on other load, what pages are in memory etc, but the number of "logical reads" performed by the query. You can get that information from the profiler or using "set statistics io on".
May 2, 2003 at 1:02 am
quote:
IMHO it is better to investigate why the optimizer preferred the clustered index to the index that you felt was more suited.
Even though I appreciate Brendon's comments and sometimes share his excitement about query hints, I am puzzled why the query optimizer did not use the more appropriate index. Since an explicit query hint made the query faster, there's no doubt the index is the one to choose, so why did the optimizer pick the wrong one? Does it neccessarily mean the statistics were not up to date ?
As far as I know, people say it's difficult to outperform the optimizer manually and generally advise to "think twice" before using query hints. Does anyone have rules of thumb what things make life difficult for the optimizer ? When is automtic optimizing good, when is it bad ?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply