SQL Performance Tuning

Chapter 1. Facilis Descensus Averni[1][1] Literally, "the descent into Hell is easy" . . . it's getting back out again that's hard! From Virgil. When we decided to write this book, we...

Chapter 2. Simple Searches In this chapter, we'll talk about syntax-based optimizing and simple search conditions. A syntax is a choice of words and their arrangement in the SQL stat...

Chapter 3. ORDER BY The ORDER BY clause causes sorts. Sorts take time. In this chapter, we'll look at how much time, and what you can do about it. Some of this chapter is also releva...

Chapter 4. GROUP BY Because GROUP BY can involve a sort, much of what we said about ORDER BY columns in the previous chapter applies for GROUP BY arguments too—keep 'em short and keep...

Chapter 5. Joins There was once a simple rule for joins—Index all the columns. As DBMSs have grown more sophisticated, and different join plans have become part of every DBMS's reperto...

Chapter 6. Subqueries There seems to be a Law of DBMS Development that states—In version 1.0 the subquery support will be bad or nonexistent, and it will slowly get better. Witness t...

Chapter 7. Columns When you're defining a column, the first question you should ask is—What data is possible or impossible for this column? For example, a "phone number" column should...

Chapter 8. Tables Performance depends on time. Time is physical. To see how table access depends on time, we must look at the physical storage of tables. The physical storage is on dis...

Chapter 9. Indexes "Indexes make SELECTs faster, but they make UPDATEs slower." —Common knowledge The common knowledge about indexes is true—but it's too vague for our purpos...

Chapter 10. Constraints As a programmer, you create database constraints so that garbage won't go in, and therefore less garbage will come out—you hope. Is garbage-free data itself an...

Chapter 11. Stored Procedures A stored procedure is something that is both stored (that is, it's a database object) and procedural (that is, it can contain constructs like IF, WHILE, B...

Chapter 12. ODBC On the Web, the www.sourceforge.net site hosts about 30 myriads of open source projects. One day we extracted the projects in sourceforge's category "DBMS Front End" a...

Chapter 13. JDBC Sun says that JDBC is not an acronym for anything, and www.sun.com says it stands for Java Database Connectivity. Go figure. Anyway, JDBC is the de facto standard API...

Chapter 14. Data Changes The term data change appears in the SQL Standard document. It means any change to the database that is caused by INSERT, UPDATE, or DELETE statements. We often...

Chapter 15. Locks Here's a locking analogy to consider. Think of an office with men's and women's washrooms. Doubtless a man could actually lock the men's washroom when he goes in, b...

Chapter 16. Clients and Servers The simplest client/server system has a client and a server, as Figure 16-1 illustrates. Commonly, though, a system involves more than one client and in...

Chapter 17. Cost-Based Optimizers Supposedly, a rule-based optimizer (RBO) differs from a cost-based optimizer (CBO). Consider this SQL statement: SELECT * FROM Table1 WHER...