wbrianwhite (8/24/2009)
Michael Ross (8/24/2009)
Rerunning queries multiple times and clearing the cache? Is that REALLY necessary every time? That just leads me to believe that the developer really doesn't know what they are doing, and they are merely hunting and pecking towards a faster result with little regard for data accuracy.Once you run the query once, all the relevant indexes are likely to be in memory in SQL Server's cache, and every subsequent query may come back in 0.1 seconds, which will totally mask the problem that the first time you run the query it may take 5 seconds to run. It will also completely mask any significant changes in performance you get as a result of changing the query. It is really necessary to do that every time.
Look at the execution plan, look at and think about what you are typing. Having complex queries will benefit from compiling and recompiling a stored procedure. This is because having an overview of a complex query is best described as impossible. Breaking it down in separate parts and reviewing their individual impact on the complete picture.
But again I have to agree with Michael, a lot of times developers don't have a clue what they are doing. 'I need to get such and such data' is the mainstream thought. Not the how, why and when.
As long as the how, why and when are not answered most of the times, the solution is crap and a disaster waiting to happen.