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

5.1 Why a New Method?

Since I am asking for your patience, I
begin with a discussion of why this tool is needed. Why not use a
tool you already know, like SQL, for solving performance problems?
The biggest problem with using SQL for tuning is that it presents
both too much and not enough information to solve the tuning problem.
SQL exists to describe, functionally, which columns and rows an
application needs from which tables, matched on which join
conditions, returned in which order. However, most of this
information is wholly irrelevant to tuning a query. On the other
hand, information that is relevant, essential even, to tuning a
queryinformation about the data distributions in the
databaseis wholly missing from SQL. SQL is much like the old
word problems so notorious in grade-school math, except that SQL is
more likely to be missing vital information. Which would you find
easier to solvethis:

While camping, Johnny cooked eight flapjacks, three sausages, one
strip of bacon, and two eggs for himself and each of his friends,
Jim, Mary, and Sue. The girls each gave one-third of their sausages,
25% of their flapjacks, and half their eggs to the boys. Jim dropped
a flapjack and two sausages, and they were stolen by a raccoon.
Johnny is allergic to maple syrup, and Mary had strawberries on half
her flapjacks, but otherwise everyone used maple syrup on their
flapjacks. How many flapjacks did the kids eat with maple syrup?

or this:

(8+(0.25 x 8)-1)+(0.75 x 8/2)+(0.75 x 8)=?

The query diagram is the bare-bones synthesis of the tuning
essentials of the SQL word problem and the key distribution data
necessary to find the optimum execution plan. With the bare-bones
synthesis, you lose distracting, irrelevant detail and gain focus on
the core of the problem. The result is a far more compact language to
use for both real-world problems and exercises. Problems that would
take pages of SQL to describe (and, in the case of exercises, days to
invent, for realistic problems that did not illegally expose
proprietary code) distill to a simple, abstract, half-page diagram.
Your learning rate accelerates enormously with this tool, partly
because the similarities between tuning problems with functionally
different queries become obvious; you recognize patterns and
similarities that you would never notice at the SQL level and reuse
your solutions with little effort.

No tool that I know of creates anything like the query diagram for
you, just as no tool turns math word problems into simple arithmetic.
Therefore, your first step in tuning SQL will be to translate the SQL
problem into a query diagram problem. Just as translating word
problems into arithmetic is usually the hardest step, you will likely
find translating SQL tuning problems into query diagrams the hardest
(or at least the most time-consuming) step in SQL tuning, especially
at first. However, it is reassuring to consider that, although human
languages grew up haphazardly to foster communication between complex
human minds, SQL was designed with much more structure to communicate
with computers. SQL tuning word problems occupy a much more
restricted domain than natural-language word problems. With practice,
translating SQL to its query diagram becomes fast and easy, even
something you can do quickly in your head. Once you have the query
diagram and even a novice-level understanding of the
query-diagramming method, you will usually find the tuning problem
trivial.

As an entirely unplanned bonus, query diagrams turn out to be a
valuable aid in finding whole classes of subtle application-logic
problems that are hard to uncover in testing because they affect
mostly rare corner cases. In Chapter 7, I discuss
in detail how to use these diagrams to help find and fix such
application-logic problems.

In the following sections, I describe two styles of query diagrams:
full and simplified. Full diagrams include all
the data that is ever likely to be relevant to a tuning problem.

Simplified
diagrams are more qualitative and exclude data that is not usually
necessary. I begin by describing full diagrams, because it is easier
to understand simplified diagrams as full diagrams with details
removed than to understand full diagrams as simplified diagrams with
details added.