SQL Performance explained by Markus Winand is a book which has a very bold tagline. This tagline is:
Everything developers need to know about SQL performance
The book has 192 pages and it covers all major SQL databases (MySQL, Oracle, PostgreSQL, and SQL server). To make matters a little bit more interesting, it concentrates on one thing: indexing. To be more specific, it covers only the most important index type: the B-tree index.
When we think about the bold tagline, the length of the book, and the fact that it covers four major SQL databases, the obvious question is this:
Can such a short book be useful to me?
Let’s move on and find out.
What to Expect
The book has eight chapters and one appendix which are described in the following:
- Chapter 1: Anatomy of an Index explains how database indexes work. This chapter gives a clean and understandable description of the structure of an index.
- Chapter 2: The Where Clause concentrates on the where clause which contains the conditions of a SQL query. To be more specific, it explains what kind of indexes we should create so that the conditions of your SQL queries don’t cause performance problems. This chapter is the longest chapter of the book and it contains a lot of useful advice. For example, I could utilize the advice concerning concatenated indexes and function-based indexes right away, and saw major improvement in the performance of my SQL queries.
- Chapter 3: Performance and Scalability describes the performance impacts of environmental changes such as data volume, system load, and "bigger" hardware. This chapter reveals that the "traditional wisdom" concerning the performance of relational databases is flawed and teaches you that the best way to reduce response time of our query is to use proper indexing.
- Chapter 4: The Join Operation describes the different join algorithms (nested loop, hash join, and sort merge) and explains how we can minimize the response time of a database query which performs a join operation. This chapter also identifies some of the performance problems caused by ORM tools and provides solutions to those problems.
- Chapter 5: Clustering Data explains the difference between a data cluster and a computer cluster, and describes how you can improve performance by clustering data to database indexes. This might sound a bit esoteric but you shouldn’t be scared of the fancy title. The chapter is well written and very practical.
- Chapter 6: Sorting and Grouping helps you to improve the performance of SQL queries which use sorting or grouping. As always, the key to success is to use indexes in the right way.
- Chapter 7: Partial Results talks about situations when you don’t need all results of your SQL query. It helps you to get the N first query results and to paginate the query results in an efficient manner.
- Chapter 8: Modifying Data reminds you that indexes will impact the performance of insert, update, and delete queries as well. This chapter describes how indexes effect the performance of these queries and helps you to fix possible performance problems.
- Appendix A: Execution Plans describes how you you can retrieve and read an execution plan which is an essential tool for optimizing the performance of your database queries. If you are not familiar with this tool, and you use one of the covered databases (MySQL, Oracle, PostgreSQL, or SQL Server), I recommend that you read this appendix as well.
So, did I like this book? Let's find out.
Get This Book
This book is self-published but you shouldn't let that scare you. It is structured in a logical way and the text is very easy and pleasant to read. I claim that if this book would be offered to any major publisher such as Manning, O'Reilly, or Addison-Wesley, they would be willing to publish it as is.
I enjoyed reading this book because
- Its advice is very actionable. You can apply the tips given in each chapter to practice when you are reading it. For example, when I followed the advice given in chapter two, I saw major improvements in the performance of my SQL queries (I hadn't read the rest of the book yet).
- It concentrates on one thing. This book is all about proper indexing. That is it. The author decided to write about one thing and, oh boy, he nailed it! This book is packed with useful and valuable advice which will help to make your queries faster than you could ever imagine.
- It proves that indexing is a development task and gives you the knowledge you need to complete that task. I have noticed that the ORM frameworks have created a false feeling of security amongst developers. Some developers think that they don’t have to know anything about relational databases because the ORM framework takes care of it. In fact, nothing could be further from the truth, and I love the fact that this book reminded me about it.
In other words, I think that every developer should read this book. If you haven't read it yet, order it right now! You won't be disappointed.