Rating: 
Summary: Best SQL tuning book
Review: "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) - 2000 buffers. If I had known about this book, I could have solved this problem in a few hours. Instead it took several DBA's, a few day's time to come up with a solution that was only marginally better. Since this method appeared to work so well for OLTP, I went back and looked at a very large OLAP query that I had worked on last year. I and several developers had spent days trying to get it to run faster. Even with the author's method, I still couldn't make it better, but I was happy to see that the join order picked by the optimizer was almost identical to what I had calculated using the book. I plan on studying this guide over and over until I've memorized this method. I don't think it will solve every problem, but I think it gives you an edge over using experience alone. Before you purchase this book... Keep in mind, that SQL tuning is fundamentally *not a simple problem*, so readers should not buy the book expecting an easy list of simple tips and tricks. (If the problem was easy, the optimizer likely would have got it right in the first place, and you wouldn't be tuning!) The correct solution to the problem is fairly complex, as complex as it *needs* to be. Also, it's not noted anywhere, but I gathered that the author assumes that the reader will have basic high school Algebra skills, and will have taken a basic statistics course in order to fully understand the reasoning behind the explanations. Although, Algebra and statistical knowledge are probably not necessary to learn the tuning method presented. I state this because I told a good friend about this book, and his first question was, "Does it have a lot of math to go through?" So, if statistics is not your thing, don't worry. The author only presents enough information to give you a level of comfort that his method is based on mathematics. He doesn't require that you know "graph theory" or anything like that. By the way, our change from 100K buffers per transaction to 1700 buffers, dropped the CPU utilization for the website that was running the OLTP query by about 70%. That was on a 16 CPU, Sun/Solaris box. So proper SQL tuning can save you a lot of $$$.
Rating:
Summary: Quick solutions for difficult optimization problems
Review: "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) - 2000 buffers. If I had known about this book, I could have solved this problem in a few hours. Instead it took several DBA's, a few day's time to come up with a solution that was only marginally better. Since this method appeared to work so well for OLTP, I went back and looked at a very large OLAP query that I had worked on last year. I and several developers had spent days trying to get it to run faster. Even with the author's method, I still couldn't make it better, but I was happy to see that the join order picked by the optimizer was almost identical to what I had calculated using the book. I plan on studying this guide over and over until I've memorized this method. I don't think it will solve every problem, but I think it gives you an edge over using experience alone. Before you purchase this book... Keep in mind, that SQL tuning is fundamentally *not a simple problem*, so readers should not buy the book expecting an easy list of simple tips and tricks. (If the problem was easy, the optimizer likely would have got it right in the first place, and you wouldn't be tuning!) The correct solution to the problem is fairly complex, as complex as it *needs* to be. Also, it's not noted anywhere, but I gathered that the author assumes that the reader will have basic high school Algebra skills, and will have taken a basic statistics course in order to fully understand the reasoning behind the explanations. Although, Algebra and statistical knowledge are probably not necessary to learn the tuning method presented. I state this because I told a good friend about this book, and his first question was, "Does it have a lot of math to go through?" So, if statistics is not your thing, don't worry. The author only presents enough information to give you a level of comfort that his method is based on mathematics. He doesn't require that you know "graph theory" or anything like that. By the way, our change from 100K buffers per transaction to 1700 buffers, dropped the CPU utilization for the website that was running the OLTP query by about 70%. That was on a 16 CPU, Sun/Solaris box. So proper SQL tuning can save you a lot of $$$.
Rating:
Summary: Practical, exhaustive text
Review: Disclaimer: I am the CTO of youDevise Ltd., a firm that distributes a product of the author's former employer, TenFold Corporation. I worked with him on performance tuning projects several years ago during his employment there. I have been running high-volume performance tuning projects for the past four years, using both SQL Server and Oracle. Our products generate their SQL, so we cannot edit it; nevertheless, our job is to make that SQL run faster, normally with hints, indexes, and parameter changes. I have observed Dan apply the method he describes in the book to help us resolve our performance problems, and it really does work as quickly and effectively as other reviewers describe. My teams and I have also tried tuning SQL ourselves, and _that_ is as difficult and ineffective as you might expect - on more than one occasion we've had to resort to hacks like denormalisation. The difference really is a few minutes versus several days. After reading the book, I finally understand what Dan was doing that let him tune our SQL so effectively. The method is highly algorithmic - you follow a series of fairly simple steps to draw a graph and out pops a near-optimal query plan. Dan also describes how to influence your database to use the query plan you found with his method. My team and I haven't had the chance to apply Dan's method yet, but it's very simple and I'm confident we will be able to do so. I plan to make the book required reading for the team before our next project. My only complaint about the book - and it's quite a minor one - is that it doesn't cover open-source alternatives like mySQL or PostgreSQL. Applying the method to these alternative databases would be fairly simple, but coverage of the basics like identifying the query plan or influencing the optimiser would be nice to have under one cover.
Rating:
Summary: A "Must Have" book for DBA's and SQL Developers
Review: 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.
Rating:
Summary: A "Must Have" book for DBA's and SQL Developers
Review: 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.
Rating:
Summary: The Best SQL Tuning book on the market
Review: 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.
Rating:
Summary: Best SQL tuning book
Review: I applied the method from the book to one of the queries used in the project I am working on (3 levels of subqueries) and successfully reduced the query time from 15 sec to less than 1 sec (I only spent 30 minutes on it). I highly recommend this book to any developer who works with SQL queries.
Rating: 
Summary: Think through your query and find a solid method.
Review: I found this book helpful, and have started to use these techniques with success. I specifically was pleased that the techniques are portable across different Database vendors. If I were the editor, I would have had the author expanded appendix A some with more application examples of the technique. But there was enough incomplete examples thru the book to get an understanding.
Rating:
Summary: Think through your query and find a solid method.
Review: I found this book helpful, and have started to use these techniques with success. I specifically was pleased that the techniques are portable across different Database vendors. If I were the editor, I would have had the author expanded appendix A some with more application examples of the technique. But there was enough incomplete examples thru the book to get an understanding.
Rating:
Summary: Great coverage, an essential reference
Review: If you are a DBA or developer, working daily with databases that support SQL, you need this book. It covers in better detail than any other book I've seen, all the behind the scenes aspects of SQL. What happens when you join, union or correlate queries? How do you know you're hitting your indexes? What new indexes do you need? Unlike most IT reference books, this one should be read cover to cover but it holds up well as a quick reference on a topic as well. Together with O'Reilly's SQL pocket guide by Jonathan Gennick, you have a formidable tool for creating and refining good SQL statements.
| 
