O’Reilly news

"SQL Tuning": The No-Guesswork Method for Generating Optimal Execution Plans

November 24, 2003

Sebastopol, CA--Early on, Dan Tow, author of the just-released SQL Tuning (O'Reilly, US $39.95) realized that the biggest factor in the performance of a business application is the speed of the SQL it runs. It took him a little longer to realize just how much room for improvement typically lies in that SQL--in fact, that the SQL that most affects the load on a system and the productivity of its end users can usually be improved by a large factor, often by a factor of two or more. The problem was finding reliable guidance on just how to tune SQL.

There are two basic issues that most people focus on when tuning SQL: how to find and interpret the execution plan of an SQL statement and how to change SQL to get a specific alternate execution plan. Tow provides the answers to these questions in "SQL Tuning" and addresses a third, even more critical question: How do you decide which execution plan a query should use?

The process involved is usually one of frustrating trial and error. As Tow explains, "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 strange city without a http://www.oreilly.com/catalog/sqltuning/map: working eyes and legs are not enough to take you where you need to go."

"SQL Tuning" details a diagram-based method for tuning SQL statements. The method is not iterative and random, but deterministic: you follow a well-defined process once, and at the end of that process you will know the optimal execution plan for the statement in question. The method involved not only leads you to an optimal plan quickly and in one pass, but also assures you that it is, indeed, the optimal plan. This last point is important because you'll know when to stop spending time trying to improve the execution of a SQL statement and seek improvement through other means, such as changes to indexes, the database design, or the application using the SQL.

"Dan doesn't guess," writes Jonathan Gennick in his foreword to the book. "He doesn't execute an iterative guess-loop, trying one idea after another in the hope of stumbling across an improvement. Instead, Dan uses an innovative and mathematically based diagramming method to derive the optimal, or near-optimal, execution plan for a SQL statement. Then he puts that plan into effect. And that's it. There's no guesswork, and there's no uncertainly as to whether further improvement is possible."

Written for readers who already know SQL and have an opportunity to tune SQL or the database where the SQL executes, "SQL Tuning" includes specific techniques for tuning on Oracle, Microsoft SQL Server, and IBM DB2. But since the book focuses on finding the optimum path to the data--which, according to Tow, is virtually independent of the database vendor--most of the book covers a vendor-independent solution to the problem.

The perfect companion to O'Reilly's "SQL in a Nutshell," this book will arm anyone who troubleshoots poorly tuned applications with a reliable and deterministic method for tuning their SQL queries.

Additional Resources:

SQL Tuning
Dan Tow
ISBN 0-596-00573-3, 314 pages, $39.95 US, $61.95 CA, 28.50 UK
1-800-998-9938; 1-707-827-7000

About O’Reilly

O’Reilly Media spreads the knowledge of innovators through its books, online services, magazines, and conferences. Since 1978, O’Reilly Media has been a chronicler and catalyst of cutting-edge development, homing in on the technology trends that really matter and spurring their adoption by amplifying “faint signals” from the alpha geeks who are creating the future. An active participant in the technology community, the company has a long history of advocacy, meme-making, and evangelism.

Email a link to this press release