SQL Tuning [Electronic resources] نسخه متنی

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, these types of mistakes make it easy to write
incorrect queries. In this chapter, you'll learn
about anomalies that you might see in query diagrams that can alert
you to the strong possibility of an error in a query or in design.
You will also see how to fix these functional or design defects,
sometimes fixing performance as a side effect. These fixes usually
convert the query to simple form, or at least to a form that is close
enough to simple form to apply the methods shown earlier in this
book.

Some complex queries go beyond any form I have yet explained how to
diagram, using subqueries, views, or set operations such as
UNION and UNION ALL. These
complex queries are usually fine functionally and are fairly common,
so you need a way to diagram and optimize them, which you can do by
extending the earlier methods for simple queries.