Recently I've read the book SQL Performance Explained from Markus Winand (@MarkusWinand) and want to give you a review of this excellent book about SQL performance.
The book contains the following chapters:
- Anatomy of an Index
- The Where Clause
- Performance and Scalability
- The Join Operation
- Clustering Data
- Sorting and grouping
- Partial Results
- Modifying Data
- Execution plan (appendix)
The author starts with the most important thing you have to understand when it comes to SQL performance: the index data structure. You'll get a precise introduction (with graphical support) of what the index is used for and how it works internally. This knowledge is required to understand the pitfalls and optimizations in further chapters.
Next, the author explains (using simple examples) when an index is used within the WHERE clause and the importance of the order of the attributes within the index and the query. For all examples, you'll see the SQL execution plan before and after the optimization to understand what the database is doing internally to gather the requested data. At the end of the book, you'll also be able to read the SQL execution plan as this is always a good entry point for optimizing slow queries.
During the book, Oracle, MS SQL, MySQL, and PostgreSQL are used as reference databases and whenever there is a slight difference for e.g. the SQL syntax, the author adds information about it.
Another nice part is that the book demystifies a lot of common myths about SQL performance. You'll get hints on how to maintain fast response times with growing data sets due to the correct usage of an index.
In addition, you'll get a good explanation of the different JOIN types with their pros and cons. Furthermore, at several places, the usage of an ORM (e.g. Hibernate) within your application is discussed and possible pitfalls are explained and optimization hints are given.
One of the main learnings for me was the correct use of an index to pipeline ORDER BY and GROUP BY queries and improve performance, which I wasn't aware of until now.
The following topics are also discussed: Index-organized tables (IOT), Usage of an index for INSERT/UPDATE/DELETE and correct pagination.
To summarize it, the SQL Performance Explained book is the best SQL related book I've read so far and with nearly 200 pages of perfect size. As a software developer, you'll get everything you need to know for optimizing your queries without needing deep knowledge about your database internals. I wouldn't recommend this book to a beginner, as it covers some advanced topics and you should be familiar with database techniques and general knowledge about indexing.
The book is available in English and German for a really fair price. In addition, the author is the creator of the pages Use the index, Luke! and Modern SQL both containing valuable information about SQL, databases and best practices.
Have fun optimizing your SQL performance,
Phil
