SQL Performance Explained Everything Developers Need to Know about SQL Performance

✍ Scribed by Markus Winand

• Publisher: Markus Winand
• Year: 2023
• Tongue: English
• Leaves: 369
• Category: Library

✦ Synopsis

SQL Performance Explained helps developers to improve database performance. The focus is on SQL—it covers all major SQL databases without getting lost in the details of any one specific product.

Starting with the basics of indexing and the WHERE clause, SQL Performance Explained guides developers through all parts of an SQL statement and explains the pitfalls of object-relational mapping (ORM) tools like Hibernate.

Topics covered include
• Using multi-column indexes
• Correctly applying SQL functions
• Efficient use of LIKE queries
• Optimizing join operations
• Clustering data to improve performance
• Pipelined execution of ORDER BY and GROUP BY
• Getting the best performance for pagination queries
• Understanding the scalability of databases

Its systematic structure makes SQL Performance Explained both a textbook and a reference manual that should be on every developer’s bookshelf.

Covers all major databases: Oracle® Database, SQL Server®, PostgreSQL, MySQL

What readers say
“This book is definitively worth having in the company library.” — Joe Celko

✦ Table of Contents

............................................................................................ vi

1. Anatomy of an Index ...................................................................... 1
   The Index Leaf Nodes .................................................................. 2
   The Search Tree (B-Tree) .............................................................. 4
   Slow Indexes, Part I .................................................................... 6

2. The Where Clause ......................................................................... 9
   The Equality Operator .................................................................. 9
   Primary Keys ....................................................................... 10
   Concatenated Indexes .......................................................... 12
   Slow Indexes, Part II ............................................................ 18
   Functions .................................................................................. 24
   Case-Insensitive Search Using UPPERor LOWER.......................... 24
   User-Defined Functions ........................................................ 29
   Over-Indexing ...................................................................... 31
   Parameterized Queries ............................................................... 32
   Searching for Ranges ................................................................. 39
   Greater, Less and BETWEEN..................................................... 39
   Indexing LIKEFilters ............................................................. 45
   Index Merge ........................................................................ 49
   Partial Indexes ........................................................................... 51
   NULLin the Oracle Database ....................................................... 53
   Indexing NULL....................................................................... 54
   NOT NULLConstraints ............................................................ 56
   Emulating Partial Indexes ..................................................... 60
   Obfuscated Conditions ............................................................... 62
   Date Types .......................................................................... 62
   Numeric Strings .................................................................. 68
   Combining Columns ............................................................ 70
   Smart Logic ......................................................................... 72
   Math .................................................................................. 77

3. Performance and Scalability ......................................................... 79 Performance Impacts of Data Volume ......................................... 80 Performance Impacts of System Load .......................................... 85 Response Time and Throughput ................................................. 87

4. The Join Operation ....................................................................... 91
   Nested Loops ............................................................................ 92
   Hash Join ................................................................................. 101
   Sort Merge .............................................................................. 109

5. Clustering Data ........................................................................... 111
   Index Filter Predicates Used Intentionally ................................... 112
   Index-Only Scan ........................................................................ 116
   Index-Organized Tables ............................................................. 122

6. Sorting and Grouping ................................................................. 129
   Indexing Order By .................................................................... 130
   Indexing ASC, DESCand NULLS FIRST/LAST...................................... 134
   Indexing Group By .................................................................... 139

7. Partial Results ............................................................................ 143
   Querying Top-N Rows ............................................................... 143
   Paging Through Results ............................................................ 147
   Using Window Functions for Pagination .................................... 156

8. Modifying Data .......................................................................... 159
   Insert ...................................................................................... 159
   Delete ...................................................................................... 162
   Update .................................................................................... 163

A. Execution Plans .......................................................................... 165
Oracle Database ....................................................................... 166
PostgreSQL ............................................................................... 172
SQL Server ............................................................................... 180
MySQL ..................................................................................... 188

Index ............................................................................................. 193 v