SQL Tuning

Chapter 7. Diagramming and Tuning Complex SQL Queries

There is no royal road to geometry.

—Euclid Proclus’s Commentary on Euclid, Prologue

So far, you have seen how to diagram and tune queries of real tables when the diagram meets several expectations applicable to a normal business query:

• The query maps to one tree.

• The tree has one root, exactly one table with no join to its primary key. All nodes other than the root node have a single downward-pointing arrow linking them to a detail node above, but any node can be at the top end of any number of downward-pointing arrows.

• All joins have downward-pointing arrows (joins that are unique on one end).

• Outer joins are unfiltered, pointing down, with only outer joins below outer joins.

• The question that the query answers is basically a question about the entity represented at the top (root) of the tree or about aggregations of that entity.

• The other tables just provide reference data stored elsewhere for normalization.

I have called queries that meet these criteria simple queries, although, as you saw in Chapter 6, they can contain any number of joins and can be quite tricky to optimize in rare special cases of near-ties between filter ratios or when hidden join filters exist.

Queries do not always fit this standard, simple form. When they do not, I call such queries complex. As I will demonstrate in this chapter, some complex queries result from mistakes: errors in the database design, the application design, or in the implementation. Often, ...