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

1.2 Who Should Tune SQL?

So, you are persuaded that SQL tuning
is a good idea. Should you be the one to do it, on your system?
Chances are that you originated at most a small fraction of the SQL
on your system, since good-sized teams develop most systems. You
might evenlike me, in most of my own historybe looking
at an application for which you wrote none of the SQL and were not
even responsible for the database design. I assumed for years that
the developers of an application, who wrote the SQL, would always
understand far better than I how to fix it. Since I was responsible
for performance, anyway, I thought the best I could do was identify
which SQL statements triggered the most load, making them most
deserving of the effort to tune them. Then it was (I thought) my job
to nag the developers to tune their own highest-load SQL. I was
horribly, embarrassingly, wrong.

As it turns out, developers who
tune only their own SQL are at a serious disadvantage, especially if
they have not learned a good, systematic approach to tuning (which
has been lacking in the literature). It is hard to write a real-world
application that works, functionally, even without worrying about
performance at all. The time left over for the average developer to
tune SQL is low, and the number of self-built examples that that
developer will have to practice on to build tuning expertise is also
low.

The method this book teaches is the best I know, a method I designed
myself to meet my own needs for tuning SQL from dozens of
applications other people wrote. However, if you really want to be a
first-rate SQL tuner, the method is not enough. You also need
practicepractice on other people's SQL, lots
of other people's SQL, whole applications of SQL.
But how do you cope with the sheer complexity of entire applications,
even entire applications you hardly know? Here is where SQL delivered
me, at least, a great surprise: you do not need to understand other
people's SQL to tune it!

Treat SQL as a speca clear and
unambiguous declaration of which rows of which tables the application
requires at some particular point in a program. (SQL is clear because
it was designed for casual use by nonprogrammers. It is necessarily
unambiguous; otherwise, the database could not interpret it.) You do
not need to know why the application needs those rows, or even what
those rows represent. Just treat the rows and tables as abstract,
even mathematical, entities. All you need to know or figure out is
how to reach those rows faster, and you can learn this by just
examining the SQL, tables, and indexes involved, with simple queries
to the database that are completely independent of the semantic
content of the data. You can then transform the SQL or the database
(for example, by adding indexes) in simple ways that guarantee,
almost at the level of mathematical proof, that the transformed
result will return exactly the same rows in the same order, but
following a much better, faster path to the data.