Simple Rules for Better SQL

I suppose I am not the only person facing this problem. From time to time, you come across some report which is running very slow. Or just a stand-alone SQL statement, which somebody in your company needs to execute once a month, and they might not even remember who wrote it. And you look at 200 lines of code, and think – how I can even start to optimize it? 

It usually helps if a person who wrote the code is still around. Even better, if this report was written just recently and the requirements are still fresh in people’s minds. Still – what would you do, if you have very limited time, very convoluted SQL, and you need to figure out how to make it better so that it won’t block ten other processes? 

For a while, I’ve been trying to come up with simple guidelines for report writers, which would help to ensure that their reports perform decently without anybody spending ten times more time. It turned out that a very short list of rules can cover pretty much all the situations. We’ve been using these guidelines for a while now, and I am pretty happy with the results. Here is the list:

• For each report, figure out whether it is an “exception” report or a “summary” report. Exception means that we are looking for some errors, for abnormal execution. Such reports should return nothing if everything goes normally. Summary means that we want to process a large number of records and calculate some results, something like quarterly earnings. 
• If a report in question is an “exception report,” define the criteria for the exception (what makes this report an exception). Make sure there is an index that can be used to select rows based on this criterion and make sure that this criterion is applied first in the execution plan. 
• If a report is a summary, look for a way to update it incrementally. Make sure that there is an index to facilitate this search (most often, the increment will be by the time last updated, and this field should be indexed).

If you can find an example of the query, which does not fit into either of the categories and/or can’t be optimized using these rules – let me know 🙂