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

10.3 Tuned Queries that Return Few Rows, Slowly

Rarely, a
fully tuned query that returns few rows, even without aggregation,
runs slowly. Since tuned queries drive from the best filter, this
implies that such a query must reach many rows at some point in the
execution plan before later filters reduce the rowcount to something
moderate. The rules of thumb this book describes are designed to make
this a rare event; in my own experience, it has happened just once or
twice per year of tuning, on average.

10.3.1 Why Queries Sometimes Read Many Rows to Return Few

Modest-rowcount queries that are slow
even after tuning can be traced to important filters that cannot all
be reached before at least one large table is reached. Consider, for
example, Figure 10-1. If the root detail table,
M, has 50,000,000 rows, then the detail join
ratios show that A1 and A2 have
10,000,000 rows each, and B1 and
B2 have 100,000 rows each. Robust, index-driven
execution plans with join order (B1, A1,
M, A2, B2) or (B2, A2,
M, A1, B1) are equally attractive, by symmetry. For
concreteness, consider just the first join order. In that plan, you
reach 100 rows in B1, 10,000 rows in
A1, and 50,000 rows in M,
A2, and B2 before you discard
all but 50 rows for failing to meet the filter condition on
B2.

Figure 10-1. A slow query that returns few rows

If you relax the robustness requirement, you can preread the 100 rows
that meet the filter in B2 and join those to the
earlier-reached rows by a hash join. You could even prejoin those 100
rows to A2 with nested loops and perform a hash
join between rows reached with nested loops to (B1, A1,
M) and rows reached with nested loops that join the
combination (B2, A2) (assuming you could get your
optimizer to produce such an exotic plan). This would reduce the
rowcount you would need to reach A2 to 10,000,
while keeping the rowcount to reach B2 at 100.
However, none of these strategies eliminates the need to read 50,000
rows from the largest, least well-cached table, M,
and that will surely take longer than you would like for a query that
returns just 50 rows.

The root of this problem is a join diagram that has selective filters
distributed across two or more branches under some large table. The
result is that the ultimate level of filtration (a net filter ratio
equal to the product of the individual filter ratios) cannot even be
approached until after the database has already reached the large
table. The highly selective combination of filters is reached too
late in the execution plan to avoid excessive reads.

10.3.2 Optimizing Queries with Distributed Filters

To make optimum use of distributed filters, you need to somehow bring
them closer together in the query diagram, preferably into a single
table. Consider Figure 10-1 again. Assume that each
of the filters on B1 and B2 is
an equality condition on some column of that
filter's table. A normalized database design places
the filter column for B1 where it is because it
encodes a property that you need to specify only once per
B1 entity. Furthermore, this is a property that
you could not infer from knowledge of entities for any master tables
that join one-to-many to B1. If you placed this
column in A1, that would be a
denormalization, defined as a property you could
infer from knowledge of A1's
matching master entity stored in B1. Such a
denormalization would require redundant data to be stored in the
A1 table, since all 100 A1
entities (on average) that join to any particular
B1 row must have the same value for this inherited
column. However, in principle, all properties of master-table
entities are inherited properties of the detail entities that match
those master table entities.

The form of denormalization I describe is not the only form of denormalization there is. Whole books are written on the subject of normalizing databases, but this simplest form of denormalization is the only form pertinent to this discussion.

For example, if Customer_ID is a property of
Orders, it is also an inherited property of the
Order_Details that match those orders. With
denormalization, you can always push properties up the join tree to
nodes above the nodes that own the properties under normalized
design. Such inheritance of filter properties need not stop at the
first level. For example, Customer_Name, a
normalized property of Customers, could be
inherited up two levels, through Orders, all the
way to Order_Details.

This possibility of pushing filter columns up the join tree as high
as you wish points to the ultimate solution to performance problems
of distributed filters. To avoid distributed-filter problems, keep
pushing the most selective filter conditions upward until they come
together in a single node, which will inherit a combined filter that
has the product of the original filter selectivities. In the extreme
case, all filters rise all the way to the root detail node, and the
query will read only the few rows it ultimately returns from that
table and join downward to the same number of rows from the
downstream master tables. In the problem shown in Figure 10-1, table M acquires two
denormalized columns, from B1 and
B2, respectively. The combined filter on these two
columns has a selectivity of 0.000001 (0.001 x 0.001), or one row out
of 1,000,000, as shown in Figure 10-2. The optimum
execution plan for that query diagram reads 50 rows from
M and does nested loops, through primary-key
indexes, to the 50 rows from each of the other tables,
A1, A2, B1,
and B2. This is a very fast plan.

Figure 10-2. Promoting filters to higher tables, using denormalization

It might seem odd to mention this powerful technique for combining
filters so late in the book.
Many texts assume that denormalization is
a ubiquitous necessity, but I have not found this to be the case at
all. On most applications I have tuned, I have not needed to add a
single denormalization for tuning purposes, and tuning purposes are
the only good reason for denormalization. If you follow the
techniques of the rest of this book, you too should have to use
denormalization only rarely, only when you identify a specific
important query that cannot otherwise be made fast enough.

Many queries can be made faster through denormalization, but that is not the point. The point is that, even without denormalization, almost all queries can be made fast enough, with correct tuning. An improvement of a few milliseconds does not justify the costs of denormalization. Most performance-driven denormalizations deliver little more than a tiny improvement versus the best optimization possible without denormalization.

If done perfectly, with rigorously designed database triggers that
automatically keep the denormalized data perfectly in sync,
denormalization can be functionally safe. However, it is not free; it
takes up disk space and slows inserts, updates, and deletes that must
fire the triggers that keep the data in sync. Given the unavoidable
costs of denormalization, I recommend it only when you find a
specific, high-priority SQL statement that cannot be made fast enough
by other means, which is a very rare case .

Time goes, you say? Ah no! Alas, Time stays, we go.

Henry Austin Dobson, The Paradox of Time