Ok, you've clicked on the run button twenty minutes ago and the query is still running. You really don''t want to stop and do serious performance tuning but you're getting that sinking feeling that you may celebrate your next birthday before this query comes back. You start to sweat, your mind is racing and although you are not a woman in labor you start to practice the lamas breathing techniques you once learned. Then you remember the article you saw about SQL Performance Tuning for programmers who don't like to do performance tuning and you smile.
Ok. Confession, I really don't like performance tuning. I just like fast queries. However, I recently had a project with a number of performance challenges and I learned some important things that I did not want to forget so I wrote them down and I would like to share them with you so you may benefit from my efforts.
SQL tuning is more an art than a science and requires a lot of trial and error.
Using the SQL Query Analyzer
Test In Batches
Make sure statistics are up to date.
Clustered indexes are NOT always better
Brute force can be a good option
Verify your performance changes do NOT affect the output
When all else fails, consider using hints
An indexed view may be the answer
If you have a slow running query that involves a join of several tables
These are just some things I learned on this project and I'm sure I have a lot more to learn. If you have more suggestions, please add your own article.