When should you start tuning a database query? I ask this question as a follow-on to my editorial yesterday, where I recommend tuning your database first, then look into tuning your queries. Tuning your database may help multiple queries perform better.
In a complex query based on a highly normalized database it is not uncommon to require joins to many tables in order to fulfill your requirements. A rule of thumb I have followed, starting way back in SQL Server 4.21a, has been to start breaking down a query when you find you are joining more than eight tables. Since SQL Server 2008 R2 I am consistently finding this rule to no longer be reliable. Computers have more resources than in the old days, and take advantage of that power making it much more difficult to beat the query optimizer for performance.
Even still, a person can beat the query optimizer in performance, even with up to date statistics, appropriate schemas, and excellent index design. I’m finding less need for this kind of tuning because more and more of the business logic is being shifted to other tiers that scale out more effectively.
In the last six years I have had to optimize less than a dozen queries. All of them were the result of either a bad database design, or requiring the database to manage a load for which it was not designed. For example, one database I worked on stored all of the parts of a query in tables. The application would pull out the different SQL Snippets, piece them together into a complete query, and then execute the defined query. While this design had a great degree of flexibility, it was not designed for performance.
In short, I am finding that, in a well-designed application, using modern storage platforms, it is not be uncommon to not require query tuning. There are definitely edge cases. A highly normalized OLTP database that must be used for reporting is more likely to need a little assistance than an application that separates transaction processing from reporting. Not all systems allow the luxury of that separation.
Cheers,
Ben