Thursday, September 10, 2015
Review of SQL Performance Explained by Markus Winand
SQL Performance Explained is a database book written by Markus Winand, the book is a little less than 200 pages. The book covers Oracle, MySQL, PostgreSQL and SQL Server. The terminology used in the book is that of the Oracle RDBMS but there are site notes for the other RDBMS products. The primary focus of this book is indexing, if you are interested in indexing then this book is for you, the one index this book covers in the B-tree Index, other indexes are not covered in this book at all.
There are 8 chapters in this book and an appendix, here is what is in the book.
CHAPTER 1 - Anatomy of an Index
The first chapter is the only one that doesn't cover SQL specifically; it is about the fundamental structure of an index. An understanding of the index structure is essential to following the later chapters — don’t skip this!
Although the chapter is rather short — only about eight pages — after working through the chapter you will already understand the phenomenon of slow indexes.
CHAPTER 2 - The Where Clause
This is where we pull out all the stops. This chapter explains all aspects of the where clause, from very simple single column lookups to complex clauses for ranges and special cases such as LIKE. This chapter makes up the main body of the book. Once you learn to
use these techniques, you will write much faster SQL.
CHAPTER 3 - Performance and Scalability
This chapter is a little digression about performance measurements and database scalability. See why adding hardware is not the best solution to slow queries.
CHAPTER 4 - The Join Operation
Back to SQL: here you will find an explanation of how to use indexes to perform a fast table join.
CHAPTER 5 - Clustering Data
Have you ever wondered if there is any difference between selecting a single column or all columns? Here is the answer — along with a trick to get even better performance.
CHAPTER 6 - Sorting and Grouping
Even order by and group by can use indexes.
CHAPTER 7 - Partial Results
This chapter explains how to benefit from a “pipelined” execution if you don’t need the full result set.
CHAPTER 8 - Insert, Delete and Update
How do indexes affect write performance? Indexes don’t come for free — use them wisely!
APPENDIX A - Execution Plans
Asking the database how it executes a statement.
So who is this book for? This book is primarily for developers, if you are an admin and you want to know about how indexing work, then this book will help you understand that. Don't expect to find how to reorganize or rebuild indexes, that is not the focus of this book.
I enjoyed this book a lot, there is a ton of useful information for developers and as a developer you should understand how data is stored and how you can get to the data more efficiently. Once you are done with this book, you will understand databases and retrieval of data much better and you might even rethink how you are storing some of the data. I also like the fact that this book is a little less than 200 pages and focused one one thing instead of those monster 1000+ page books that cover dozens of topics but don't delve deep into any of those topics.
I give this book two thumbs up because it does what the author's intentions, is focused on indexing and explains it well. I do have one little bone to pick with the book and that is that the book does not mention the XML Execution plan that was introduced with SQL Server 2005.
You can check out the book at the book's website here: http://sql-performance-explained.com/
You can also check Amazon here: SQL Performance Explained