Data Analysis Using SQL and Excel, 2nd Edition

CHAPTER 14 Performance Is the Issue: Using SQL Effectively

This chapter differs from the earlier chapters by shifting the focus from functionality to performance. The subject of performance can be highly database specific. Fortunately (and perhaps surprisingly), some general rules and considerations apply to most if not all databases. Some of these rules are based on general principles of how database engines are designed; others are based on how SQL is written; and still others on how the data is represented in the database. This chapter covers these general principles rather than database-specific optimizations.

The power of SQL arises from several factors. First, SQL can express many important data manipulations needed for analysis—the use of common table expressions and subqueries gives the language a lot of power. Second, the database engine optimizes the queries for available hardware and data. This optimization is very important: The same SQL query that runs on a small table on an Android device can run on a giant table on a massive server with many processors. The SQL statement remains the same, even though the underlying algorithms may change dramatically. Users do not have to fully understand the underlying algorithms to use SQL effectively, any more than commuters need to understand mechanics or the physics of the internal combustion engine to drive their cars to work.

This chapter discusses different ways that a simple query might be executed, to give a flavor of ...