SQL Tuning - page 7

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.