Customer Reviews

SQL Performance Tuning

5 star:		(8)
4 star:		(0)
3 star:		(2)
2 star:		(0)
1 star:		(0)

 

 

This book is rather controversial. I'll begin with a quote from the preface:

QUOTE
...we've written it for users and programmers who want to improve SQL performance, no matter what brand of SQL they use. In this book, you'll find tuning tips for common situations... Rather than exploiting the unique features of a single DBMS, we're going to give you ideas that are good for all of the major SQL DBMSs.
END QUOTE

Sounds good, but !

The biggest idea behind this book is that the authors took 8 most common databases (as per 2002: DB2, Informix, Ingres, Interbase, MS SQL, MySQL, Oracle, Sybase) and sort of compared them on many different subjects. I said "sort of" because you won't find specific recommendations like "DB2 does this better", or "this makes the query faster on Oracle". Instead, the authors came up with this idea - upon each discussed topic they summarize the gain that you might possibly get from using that particular technique should you be using it on _all_ of the 8 databases. They put it like:

GAIN: x/8

which means that with the discussed trick in place, x out of 8 databases performed better (and the rest showed no improvement).

Two things make it break - (1) there is only so many tricks that improve on most databases (it's commonly 3/8) and (2) there is no way to tell which of 8 showed an improvement.

And so, how does it help me as a database administrator and developer ? I only work with 2, may be 3 specific database servers and there is no way to tell whether _they_ gained from that trick or not. All I can do is to keep all tricks in mind, apply them all anyway and hope it was my database that had an improvement. Or, I can re-run tests and see for myself.

The word about tests - the authors did run all the tests on the same single CPU Windows NT machine and specifically say:
QUOTE
We should explain that all timing tests for this book were done with a single-CPU computer, so comparisons between DBMSs would be misleading. The results wouldn't give full points to the DBMS with the best threading mechanisms.
END QUOTE

This book would make a good source for trivia questions. Tricks and tips all around, but many discussions are too short to be of any use and end up with catch-it-all rules.

Examples:

Chapter 17: Cost-based optimizers:
About 10 pages. Says that cost-based and rule-based optimizers are different and CBO peform better because they use real statistics. And so you should update statistics and use EXPLAIN and optimizer hints. That's all. Oh no, really ?

Chapter 11. Stored procedures.
Shows very reasonably and truthfully why SPs are useful and better in many situation and what the gotchas are. But then again
QUOTE
When parameter passing is by value, the DBMS usually makes a local copy ... That leads inevitably to the bad idea of declaring parameters to be "input/output" even when they're merely "input" parameters. The trick ... is that an "input/output" parameter value is not recopied down the line if stored procedure #1 calls stored procedure #2 ... it saves you space and copying time.
END QUOTE
Bad be the idea or not, passing large amounts on a stack is certainly wrong, not only for SPs and indicates poor design. Otherwise, if you are passing small arguments, and want to optimize out the copying of a single int or varchar - what kind of a system is that ? I mean - you are calling an SP in a database. It begins a transaction, processes a lot of rows, does all the usual DB stuff and commits. And you are saving a single memory copy here ?

And the really performance-wise important topics may be mentioned but skipped, ex.

Chapter 10: Foreign Keys
QUOTE
It's technically possible that a CHECK constraint will not be violated at the time you update a row, but it will be violated by the time the SQL statement ends ... Because of these possibilities, ... DBMS will wait until the end of the statement before testing a constraint for violations. Thus, every row has to be accessed twice-once to change it, and a second time later on to test for constraint violations.
END QUOTE
Now, _that_ would be a huge performance hit sometimes. Any discussion ? Clues or hints may be ? Nope. Well, thanks for mentioning anyway (and I'm serious right now).

The book lacks any hardware-related details, ex. how CPU, memory or I/O affect things. It lacks any database-specific details. It lacks any common approach to optimization, there is no single direction in which it leads you.

Therefore, here is my bottom line for this book - a good overview, yes. Many interesting and fascinating details, yes. A lot of hints, oh yes ! Detailed, sometimes. Practical, uhm, unlikely.

There's a lot of ground to cover between being able to put together a SQL query that runs and being able to construct a query that works well. The journey is all the more complicated when you're trying to target multiple RDBMSes, each with its own nuances and oddities. That's where this book shines. By going for breadth--picking 8 major RDMBS and contrasting how they behave in the face of various SQL constructs and optimizations, plus covering some general theory, this book conveys a quality of information that vendor-specific books lack. I knew a fair amount about databases and SQL before picking up the book, and came away with a much better feeling for the terrain, and a new set of optimization tricks.

It's unfortunate, but perhaps unavoidable, that some details (e.g. lack of information on transactions and subselects in MySQL) where out-of-date by the time the book was published. It's also unfortunate that the authors were prevented by vendor license agreements from providing performance hard numbers.

Don't get this book to learn how to optimize for a particular vendor's database. Study it to broaden your skills.

I've just finished chapter 7 and 15. The book offers quite interesting information. It's written very well and easy to understand. Plus a special sense of humor ;-)
The information gathered must not be used immediately, but helps when making decisions on database design and usage. This is because, you gain an overview on what the "big eight" do, thus you better understand what your decision could mean, when switching databases.
It's a book suitable for both experts and beginners, though basic sql knowledge is expected.

Over the years I've developed a number of systems that utilized relational databases. When I developed or used a complex and/or large database, more often than not I found that database performance was the main issue, especially in real-time or near real-time applications. I have been interested in the databases *only* to the point of satisfying my projects' needs and considered them subordinate to the rest of the systems. I did realize the complexity of RDBMSs implementations, but never cared to learn more than I necessary to solve an immediate problem at hand. Maybe this is why I've never really enjoyed reading about the databases: I believed that the databases and SQL were quite boring comparing to all the elegance and slickness of C++, Java, CORBA, and other object-oriented technologies.

"SQL Performance Tuning" changed my perception of that; I was hooked just after just a few sentences. The language, the obvious depth of the authors' knowledge, the wide and careful coverage of all related issues, including the very fundamentals of the relational databases are presented as needed; and mostly, the enjoyably right balance between the theory and practice makes this book an outstanding read. I have not read most of it yet, but I am very excited about what I will find in the rest of it, even if it is something that I think I know well.

This is one of the few SQL books that has practical examples and addresses DB2, Informix, SQL Server, and Oracle. It contains excellent facts on how SQL is processes, which one must understand to write top performing SQL. The authors accomplished just that with this excellent book. You'll get hard to find details on query optimizers, B+tree index structures and many myths you have had will be demystified...

Out of 25 plus books I have read on SQL, this is the best book yet I have found for performance tuning. 90% of the programmers in our shop have puchased their own copy. If you really want to know the inside and outside of SQL tuning read this book.

This book, SQL Performance Tuning, has proved to be one of the most invaluable books in my reference collection of SQL books. Some of the more complex and unanswerable questions that have cropped up in the shop I work at have been revealed with simplistic elegance in this book. Topics covered include General Tuning, Specific Tuning, Join Plans, Join Strategies, B-Tree Structure, Indexes, Keys, Foreign Keys, Query Prepping, Locks, and this is but to name a few. One nice feature about this book is it's not vendor specific. It covers the "Big Eight" RDBMS so this book has dual value to me as I use both SYBASE and MSSQL 2000. Buy this book. It will reveal the secrets of the SQL universe to you.

Every professional programmer (and DBA) should have a good book or two on SQL. There are many to choose from, and a lot of them are very good. This book, SQL Performance Tuning by Peter Gulutzan and Trudy Pelzer, is one of the best. It provides a treasure trove of tips for improving SQL performance on all of the major DBMSs. This book does not teach SQL syntax, but instead helps the reader to understand the differences between eight DBMSs, including Oracle, DB2, SQL Server, Sybase ASE, MySQL, Informix, Ingres, and even InterBase. Throughout this book the authors present and test techniques for improving SQL performance, and grade the technique for its usefulness on the major DBMSs. If you deal with heterogeneous database implementations this book will be a great assistance, whether you are a programmer, consultant, DBA, or technical end user.

If you need a support for performance issues with any SQL this book is great.

The book gives many examples of how to fine-tune SQL statements, and usually displays performance gain/loss resulted from the fine-tuning right below the examples. But the result is shown in the format "##/8", meaning the DBMS's of ## vendors out of the "big 8" show performance gain. For me this information is not too much helpful, since I only work on Microsoft SQL Server and am not very interested in other verdors. If the book can give more vendor-specific examples and explanations it will be more helpful, at least for me.