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

Preface

The seaman's story is of tempest, the plowman's of his

team of bulls; the soldier tells his wounds, the

shepherd his tail of sheep.

Sextus Propertius Elegies

More than 10 years ago, I came to understand that the biggest factor
in the performance of a business
application is the speed of the SQL it runs. It took me longer to
realize just how much room for improvement typically lies in that
SQL. The SQL that most effects the load on a system and the
productivity of its end users can usually be improved by a large
factor, usually by a factor of two or more. However, I found little
guidance regarding just how to tune SQL. I believe that problem
persists today.

Academic journals describe detailed methods that are suitable for
automated optimization, but these methods are not adapted for manual
tuning. Documentation for the practitioner, so far as
I've seen, is incomplete. Database vendors and
independent authors document well how to review the path the database
takes to reach the data. (The path to the data is known as the
execution plan.)
Armed with the execution plan, you can understand why a query runs as
long as it does. With varied success, the documentation also covers
what you can do to change an execution plan, if you suspect that it
is not optimal. The missing part in the literature is a detailed
manual process to deduce, without endless trial and error, exactly
which execution plan you should want. Since real
business-application queries can easily offer billions of alternative
execution plans, tuning without a systematic method to choose your
target execution plan is hopelessly inefficient. The problem is akin
to finding yourself lost in a strange city without a map: working
eyes and legs are not enough to take you where you need to go.

The missing piece of the problem, how to choose the best execution
plan, turns out to be virtually independent of your choice of
database vendor. This presents a wonderful
opportunity for a single book on SQL tuning that is 80%
vendor-independent, because 80% of the book has nothing to do with
the uninteresting details of viewing and manipulating execution plans
with vendor-specific methods. I wrote this book to fulfill that
opportunity and to enable you to approach SQL tuning with this
powerful, vendor-independent approach.