SQL Tuning

by Dan Tow

Book Overview

A poorly performing database application not only costs users time, but also has an impact on other applications running on the same computer or the same network. SQL Tuning provides an essential next step for SQL developers and database administrators who want to extend their SQL tuning expertise and get the most from their database applications. There are two basic issues to focus on when tuning SQL: how to find and interpret the execution...

• Format: Paperback
• Language: English
• ISBN: 0596005733
• ISBN13: 9780596005733
• Release Date: December 2003
• Publisher: O'Reilly Media
• Length: 314 Pages
• Weight: 1.28 lbs.
• Dimensions: 0.8" x 7.1" x 9.2"

Customer Reviews

A Work of Genius

I have withdrawn my earlier review because I did not properly understand the importance of this book until the second reading. That is a difficult admission to make since my business is database performance tuning. This book is about tuning SQL queries in a systematic and scientific manner. It is above all about determining the optimum order in which the query engine should access the tables involved. My quibble was that join order is only a part of the whole tuning problem and most of the time the query optimizer got it right anyway. While this is true, I see now that it misses the point. The fact that the optimizer gets it right so often allows us to take join order for granted. But how do we know the optimizer got it right unless we know what the optimum join order is? That is what this book teaches, a methodology and an elegant system of notation that allows us to determine the optimum join order of the most complex query. As the author points out, the number of possible join orders increases factorially with the number of tables involved. An 8 table join has 40,320 possible join orders. That rules out trial and error for all but the simplest queries. It turns out that analyzing and diagramming according to Tow's method gives you a deep architectural understanding of the query and the problems that face you. It gives you a plan to which you can apply the tools of the trade, indexes, code optimization, etc. This is a book that will be on the shelf of serious performance tuning professionals for as long as SQL is the language of data manipulation.

The Best SQL Tuning book on the market

Having read most of the Oracle performance tuning books on the market, I have to say this is absolutely the best pure SQL tuning book you can buy. The author teaches a unique approach to SQL tuning. He uses 'graphs' to map out the best sql plans. Though the author is an Oracle specialist the technique works with all SQL databases. He does not provide tips for the best type of join or whether 'in is better than exists'. He teaches a repeatable methodology that works. So you can solve your SQL tuning issues without guessing. I saw one negative review of this book and found the persons comments amateurish. His complaint was essentially that the author did not include information specifically about SQL Server that Microsoft gives away for free on their website and that many other books have repeatedly rehashed.

A "Must Have" book for DBA's and SQL Developers

For many years the process of SQL Tuning has been considered an "art". SQL tuning books written to date have presented guidelines and "things to try" but "SQL Tuning" by Dan Tow is different. This is the first book I have seen that presents a clear methodology to attack the problem in a scientific/mathematical way and includes a diagramming technique to visualize the components of a query that affect performance. Dan's book has gone a long way in helping to transform SQL tuning from an art to a science.The first four chapters of the book cover some of the subjects that are found in other SQL tuning books, and focus on the important points without delving too deeply into database internals. The really "good stuff" is covered in chapters 5 through 10, which includes how to "draw" a query diagram, and the procedures to follow to derive an optimal join order. The problems/examples presented and resolved in these chapters help the reader get a start on understanding the methodology, which can then be applied to actual tuning problems as they are encountered.So far I have applied this technique to about 20 tuning problems I have encountered on the job using the Oracle RDBMS. I still consider myself a relative novice in using the methodology, but for each of the 20 problems I have been able to significantly reduce elapsed time and resource consumption (gets/reads). Also, these results were achieved in a matter or hours, rather than days. The query diagram has even facilitated finding "missing joins" in some of the queries without having a knowledge of the particular application.This book is a "must have" for DBA's and developers and I highly recommend it.

Critically necessary work

Most all database driven applications are going to use an SQL RDBMS. Whether you wrap them in an O/R mapping layer or write the SQL directly you are going to get to SQL at some time. And often you will find that you need to be able to tune and optimize some of the critical queries for the best performance. But how do you get there?SQL Tuning covers reading the execution plans, tweaking the queries and diagramming the plans so that you can understand what the server is doing and how to optimize it. This is a unique book for O'Reilly which is a publisher most at home with works that are mainly code or API references. This book instead teaches a methodology and does it well. No quick fixes or cookbook style approaches are presented. This book teaches action through a deep understanding of the topic at hand, and if you use SQL on systems that require high performance then this is an understanding relevant to you.Here are the key chapters:Chapter two presents the internal of the database in a solid introductory manner.Chapter three teaches you how to read execution plans.Chapter four teaches you how to control those plans on Oracle, DB2 and SQL Server.Chapter five teaches you a way to diagram the plans to understand the in more detail.Chapter six shows you how to analyse those plans then then turn that into a new execution optimized execution plan.As you can see, no quick fixes here. This is a book about a methodology and how to apply it. Bravo O'Reilly and Dan Tow for this important and unique work.

Quick solutions for difficult optimization problems

"Tell me something I don't already know", that's what crosses my mind every time I pick up a new performance, tuning book. If you're like me, 90% of the content in any DBA book in the store is 'old hat'.This book is refreshing. It doesn't waste time going over all of the stuff you learned years ago. Knowing how to read an execution plan or when to pick a hash join over a nested loop join is not what this book is about. There are plenty of books on the market that cover basic, vendor specific, query tuning. I personally have about 20 of these books on my bookshelf here at home. (Over the years I've worked on Sybase, SQL Server, Informix XPS, & Oracle.)Here's the deal...Anyone who has worked with really big systems will eventually run into an optimization problem that seems to be unsolvable. You can try histograms, compressed key indexes, partitioning, pre-joined indexes, and materialized views, but you still can't get the performance that's being requested. For a DBA, it can be a very frustrating dilemma. This is especially true when you know from the data volume that you should be able to get there.The truth is, optimizers can't always get the right solution, even with correct statistics. There are some good technical reasons why this is true, but that's out of scope for my review. In any case, that's where this book comes to the rescue. I feel that it gives you some insight into the optimization problem and tells you how to correct the problems that your optimizer can't figure out. This book is NOT for use on 95% of your queries. Most optimizers will pick the correct access plan if the DBA does his/her job correctly and collects the appropriate statistics.In my experience, I get two types of problems that I have trouble getting the optimizer to solve:#1. Joining together a large number (8-14) of tables. At least 1 or 2 of the tables have over 30G of real data. By `real data', I mean that 30G of data is actually populated.#2. Making high transaction queries read the fewest amount of buffers in order to get rid of latching problems.After I read the first few diagramming chapters of this book, I thought I'd give it a try on a problem that was recently solved at work. I was surprised. It worked, and even with my clumsiness with the method, it only took me about 2 hours to get a solution. It took us about 3 days at work. Our trial and error solution was slightly better, than what I came up with using the author's method. However, it was so close that had I used his method, I most certainly would have gone on to another problem. (The author's solution was strange because I would never have solved the join order the way that he did. His solution actually had me pick the largest table, out of 10, to drive the query. I thought this was odd because my test query had some very good filters on smaller tables.)These were the results per execution: Optimizer - 100K buffers.Trial & Error - 1700 buffers. Book method (basic graphing)