Motivation: New book brings practical method and insights to SQL Tuning

SQL Tuning has been likened to one part science, one part artisan craft and one part voodoo wishful thinking. Dan Tow's book, SQL Tuning from O'Reilly Press moves tuning distinctly into the science category. Methodically and pragmatically, Tow takes on the extremes in database tuning: 1)"its impossible to tune SQL code given ever-changing conditions and complex queries"; 2)"just leave the optimizing of databases to the experts - the built-in and finely tuned data optimizers available in the top-of-the-line products from IBM, Microsoft, Oracle, and Sybase"; 3)"with the new need for heterogeneous joins and unpredictable ad-hoc queries, built-in optimizers can't hope to help"; 4)"New, all-in-memory databases obviously obviate the need for time consuming tuning exercises"; 5)Embedded and ultralite databases just don't have the facilities to make tuning practical". One by one Tow punctures the myths by careful analysis of what and how optimizing and tuning of code can achieve. For example, he points out with hardware and software caches, many database applications are already all-in-memory; yet they still can benefit from proper tuning. As for automated built-in database tuning, he does not try to replace it but rather provides a means of evaluating some of the optimizers. The problem is that most optimizers are cost-based satisficers. They have two critical short comings. The automated optimizers are time-constrained; many times they give a good but not optimal solution. Second,

optimizers are myopic; they do not know that variations on this query or that update will be done in a batch process making costly index building well worth the while. True, users can pass on hints to most optimizers but the syntax is cryptic and sometimes the effort just backfires. In sum, knowing the basis of SQL Tuning means having a recourse when all else fails at the least; and better understanding of how to make SQL work for you better in general.

The second chapter on data acess basics is almost worth the price ($40US) of admission alone. Tow discusses the various data objects created by the 4 principal databases: IBM DB2, Microsoft SQL Server (and by extension Sybase SQL Server because Microsoft SQL Server was "adopted" from the Sybase product), and Oracle's Oracle Xi. In fact, throughout the book, Tow discusses all the optimizations methods in relation to these four top of the line databases. However, the query diagramming technique he uses to determine optimal queries can be applied to any SQL database which provides indexes and simple select count(*) from dTable where ...". And in fact this is the virtue of the book, Tow describes a technique that not only helps database administrators arrive at an optimal query solution for just about any SQL database or combination there of; but at the same time helps to uncover logic errors in database design - specifically on maintaining unique keys and referential integrity. This is no small benefit. This is 300 pages, with a graded set of examples, several exercises plus a nifty chapter Outside-the-Box Solutions to Seemingly Unsolvable problems. In sum, I can only echo the American Express travellers check commercials: "Dont go on a database programming trek without Tow's SQL Tuning in your toolkit."