Get Free Tech Books!
Join the DZone Review Team to share your opinion and expertise through published book reviews and Refcardz review discussions. You'll get free tech books and early access to Refcardz.
|SQL databases have become incredibly fast in parsing and executing even very complex SQL statements. But to many SQL developers, writing performing SQL is still a mystery. This book covers 90 percent of what any SQL developer should know in very simple terms. A must-read for all SQL beginners and also for pros!|
Markus Winand, creator of http://use-the-index-luke.com, busts a lot of myths by explaining in very simple terms that all you need to understand is proper indexing. His book "SQL Performance Explained," which is available also in German and French, reads like a very easy text book, starting by explaining the most popular and most common indexing structure: the B-Tree. From there on, the book puts new things in logical relation to topics already covered. As such, it helps the reader find his or her way to understanding the most complex problems faced in everyday SQL development. At the same time, the amount of information per page is extremely dense, making every sentence an important piece of the puzzle. This is quite remarkable for such a technical book, dealing with such a dry matter.
Markus proves to be very knowledgeable in his writings, not only with respect to SQL as a language itself, but also concerning software architecture in general. He puts database interaction in relation to overall scalability, and also knows his ways with ORMs in various languages. This is interesting for those developers who are using “higher levels” of abstraction over their database, and are thus facing the fine-tuning of third-party generated SQL.
The book really shines once the reader goes into the depths of understanding performance implications of proper indexing to each of the SQL SELECT statement’s various clauses. Even after years of tuning Oracle execution plans, I’ve had many “aha” effects when learning about things like:
The most interesting “aha” effect to me, however, was triggered by learning that indexes can be used for their data clustering effects. If properly designed, an index will “pre-sort” related values prior to actually executing GROUP BY, ORDER BY, or FETCH FIRST clauses. This can lead to instant ORDER BY results, if the ORDER BY clause matches the chosen index definition. Markus also busts the myth that Oracle’s Index-Organized Tables are actually useful for this purpose; they’re mostly not useful.
If clustering and pipelining effects do not apply, one can still choose to use what Markus calls the “SEEK method”, where partial results are fetched by omitting the OFFSET .. FETCH FIRST clause and using much faster regular predicates instead. The “SEEK method” has two advantages:
What I am looking forward to in a future edition of Markus’ book is some more depth in the chapters explaining the influence of indexes with respect to performance of INSERT, UPDATE, DELETE statements (and maybe MERGE?). It would also be great if some additional insight into DB2 and Sybase could be given. The current edition essentially covers the popular Oracle, PostgreSQL, SQL Server, MySQL databases.
Having read this book, I feel much more at ease with designing databases for performance and with analyzing execution plans for fine-tuning. Even with quite a bit of experience, this book has generated some new knowledge, while at the same time, I think it is very easy to read for SQL tuning beginners as well.
Or to put it in Markus’ terms, “This book should have been written 10 years ago.”
This book is available via booksellers and the author's website: http://sql-performance-explained.com.
(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)