Book Review: "SQL Tuning" by Dan Tow

Amazon doesn't let me entering review without account, so it goes here.

Optimizing a single SQL statement is the most important part in database performance arena. Unlike other performance problems it is proven to be difficult, so that no automatic solution is lurking in the future. This is why database performanca analyst would find Dan's book indispensable.

Dan headed Oracle Apps performace group for quite a while, where he got plenty of material for his book. (Apparently, the only utility of unfamous Oracle Apps being an excellent test bed for their Server product).

Written from practical experience Dan's book debunks many myths established by SQL optimization community. He introduces a concept of robust execution plan, that is the plan that may be not the optimal but which would never loose too much to the optimal solution. In overwhelming majority of OLTP cases Indexed Nested Loops (NL) is a robust execution plan, while Hash Join (HJ) and Sorted Merge Join (MJ) are not. Indeed, the often cited argument in favour of HJ or MJ is the advantage of leveraging disk sequential io vs. random io. Today's systems, however, have lots of memory, so that caching effect relieves this effect to the extent when it becomes extremely hard to find a testcase where HJ outperforms NL in a meager 10 times. Given that indexed NL routinely outperforms the other join methods at many orders of magnitude, this establishes the idea of robust execution. To be fair to theoretical community the idea of robust execution is not quite new: http://citeseer.nj.nec.com/chu99least.html

There are many other optimization ideas introduced by Dan. He also mentiones areas that have negligeable effect: sorting, grouping, post group filtering with "having", etc. Emphasis on the important aspects of SQL optimization is IMO the strongest feature of Dan's book. A reader would certainly benefit knowing what to focus attention to in overwhelming amount of information associated with query optimization problems.

The book has few missing areas. Understanding cost model is essential for performace analysis, but this topic is omitted in the book. The good references are Wolfgang Breitling's papers at www.centrexcc.com. The other large missing area is query transformations, but I'm not aware of any good source covering it anyway. Received on Wed Dec 31 2003 - 12:40:39 CST