Key Takeaways
Build performance into the design, because tuning cannot repair bad foundations
Design comes first. A query can return the right answer and still fail the business if it takes too long. Faroult argues that developers, not just database administrators, must make performance part of initial design: production tuning generally improves a system only modestly unless a major mistake is present. Tuning a particular server is not the same as designing code to remain efficient as data grows, hardware changes, and database software evolves.
Strategy beats patchwork. One factory procedure repeatedly loaded and reworked the same data, taking about 20 minutes. Reframing the task as one query that combined its sources and aggregated them reduced the run to about 20 seconds. The leap came from redesigning the work, not from a clever parameter setting.
What's striking is the shift in responsibility: performance is treated as a quality of the whole system, not a repair ticket handed to a specialist. This resembles safety engineering, where a reliable outcome depends on architecture, procedures, and operating conditions working together. A useful consequence is that performance requirements should be tested alongside correctness, with realistic data volumes and growth assumptions. The caution is that early optimization can also harden mistaken assumptions into code. The goal is not to guess the perfect future workload, but to choose sound structures and make uncertain assumptions visible and testable.
Model business facts atomically so the database can enforce their meaning
Store usable facts. An attribute is atomic when the business can search and reason about it as a whole. A single free-text safety-equipment field might say that a car has anti-lock brakes, but searches become slower, misspellings evade checks, and the database cannot reliably validate the claim. Separate, well-defined attributes let the database apply constraints. Normalization then checks that attributes depend on the whole key and not on other non-key attributes, reducing duplication and conflicting updates.
Represent distinctions directly. A customer may have a billing address, a shipping address, or several shipment destinations. Hiding these cases in nullable columns forces applications to guess what missing values mean. Separate address or subtype structures can express the distinctions clearly. The book also warns that nulls complicate SQL logic: a comparison involving unknown data can be neither true nor false, which can make a query return surprising results.
The deeper point is that a schema is an executable account of what an organization believes to be true. This connects database design to type systems: a well-specified type prevents invalid states from being represented, rather than asking every program to remember the same rules. That discipline also improves the optimizer's information about the data. Still, normalization is not a command to split everything endlessly. The right level of atomic detail depends on actual business questions, and read-heavy analytical systems may deliberately accept duplication after its costs and integrity risks are understood.
Batch database work instead of paying for a round-trip on every row
Fewer trips, more work. Connections and exchanges between an application and a database carry setup and communication costs. In one test, connecting and disconnecting for every line loaded just 7.4 lines per second. Keeping one connection and inserting groups of 100 raised the rate to 9,190 lines per second, roughly 1,200 times faster. The gain came from avoiding repeated connection setup and sending data in batches, not from changing the data itself.
Let SQL handle sets. A loop that fetches rows, makes simple decisions, and sends each result back to the database often hides work SQL can perform in one statement. Combining operations also gives the optimizer a larger task to plan. There are exceptions, especially when a single huge statement becomes unwieldy or transaction recovery costs dominate, but row-by-row processing should be a deliberate choice rather than a default habit.
This is an instance of amortization: fixed costs become less painful when spread across a larger unit of work. The same principle appears in network protocols, storage systems, and manufacturing batches. It also explains why code that looks modular at the application level can be expensive at the system level. Batching is not automatically safer, however. Large transactions can hold locks longer, consume more undo space, and complicate recovery. Effective batching therefore balances communication efficiency against transaction size and operational risk.
Keep queries declarative long enough for the optimizer to explore alternatives
State the result, not the route. SQL works best when a statement describes what rows are wanted and leaves the database engine room to choose how to find them. The optimizer can transform equivalent relational expressions, using indexes, table layouts, statistics, and available memory to select an execution plan. Application code that splits one logical task into many dependent queries prevents the optimizer from reasoning across the whole task.
Know where freedom narrows. Sorting, ranking, and limiting results impose an order that relational sets do not have. In Oracle, applying a row limit before sorting can return five arbitrary eligible employees rather than the five highest paid. The safe pattern is to sort first and limit the resulting ordered set. User-written functions that conceal database queries can similarly hide work from the optimizer.
The optimizer is a bounded decision-maker, not an oracle. Its advantage comes from comparing alternatives when the query exposes the problem clearly and the database contains trustworthy statistics and constraints. This resembles compiler optimization: a compiler can rearrange operations only when it can prove that behavior is preserved. The practical lesson is to preserve semantic freedom where possible, then measure the result. Declarative SQL does not guarantee an excellent plan, and presentation requirements such as ranking necessarily restrict what can be rearranged.
Index for real access paths, not every column that might be searched
An index is a trade. It can speed up targeted reads, but every insert, delete, or change to an indexed value may require index maintenance. In a 33 GB accounting database cited in the book, indexes accounted for more than three quarters of the combined table and index space. Tests also showed that index maintenance could add costs comparable to inserting into the table itself. This is why a heavily indexed, frequently updated table deserves scrutiny.
Match the query to the key. A composite index can serve searches using its leading columns, but a function applied to an indexed column often prevents ordinary index use. For example, applying a date-truncation function to a timestamp can be replaced with a bounded date range. That preserves the column's index access and makes the intended interval explicit. Index usefulness also depends on where matching rows sit physically, not only on how many match.
Indexes are often discussed as if reads were free and writes were an afterthought. The book's accounting of their storage and maintenance makes the opportunity cost concrete: each shortcut for a reader creates work for the system that changes the data. This is a portfolio problem, since one index may help a critical query while harming overall write throughput. The simple rule is not to minimize indexes blindly, but to connect each one to an observed workload and periodically reconsider it as that workload changes.
Cluster reads, spread writes, and partition only for a measured workload
Physical layout changes the game. Rows placed near one another can make range queries much faster. In one test, a clustered organization made a range scan about 20 times faster, but random insertion into that structure ran at roughly half the rate of insertion into a regular table. A design that helps readers can make writers pay through extra movement and contention.
Partition by a useful property. Splitting a table by date can let the database ignore irrelevant time ranges, especially when historical data is large and queried selectively. But partitioning by the current date may send every concurrent insert to the same hot area. Analytical star schemas make a different trade: denormalized dimensions and many indexes can simplify read queries, while making data preparation and loading more involved. Physical design should serve the system's mix of work, not one attractive query.
This is a classic operations-research problem: local efficiency can reduce global efficiency when workloads compete for the same resources. A storage strategy has to be evaluated across read patterns, write patterns, concurrency, and maintenance windows. The book's contrast between clustered reads and scattered writes is especially useful because it prevents a common mistake, judging a physical design from one benchmark alone. Modern workloads also shift over time, so measured benefit should be revisited rather than treated as a permanent property of the schema.
Drive each query from its strongest filter, then aggregate once
Let selectivity choose the route. Consider finding Gotham customers who bought a Batmobile recently. If few customers live in Gotham, starting from customers may be effective. If few customers bought that item, starting from its order lines may be better. The right direction depends on data distribution, available indexes, and how those facts change over time. A query's written order is not necessarily its best execution order, but its structure can influence the optimizer.
Remove waste early. Filtering before a sort or aggregation reduces the data that later steps must handle. In one accounting example, three separate views each scanned the same table to calculate related totals. Conditional aggregation produced the required counts in one pass and ran about four times faster. Correlated subqueries deserve particular care when they repeat the same lookup for many outer rows.
The book makes query design an exercise in asking, 'Where is the narrowest entrance into this data?' That is more durable than memorizing a preferred join syntax. It aligns with query-planning methods across information retrieval, where candidate sets are reduced before expensive ranking or combination. Yet selectivity estimates can be wrong, especially when data is skewed or statistics are stale. The practical method is to form a hypothesis from the data, inspect actual work, and test representative parameter values rather than trusting a universal rule.
Test query throughput under load, because isolated speed hides the queue
A fast result can still be costly. On one test machine, a lookup using an indexed column of a 50,000-row table could run about 5,000 times per second, while a scan based on an unindexed column managed about 25. A single run made the difference hard to notice, but repetition revealed a large capacity gap. Under concurrent load, slower queries occupied service capacity and delayed faster ones too.
Measure the whole transaction. Locking can serialize changes to shared rows, and long transactions keep those locks unavailable for longer. Repeated queries, unnecessary checks, and round-trips also increase the time each request occupies the system. Performance tests should therefore measure business throughput, such as orders processed per hour, as well as response time, and should include realistic concurrency and peak loads.
Queueing theory clarifies why tiny inefficiencies can suddenly become visible: when requests arrive near the system's service capacity, even a small increase in average work can create long waits. The visible symptom may be that everything is slow, even though only some operations are inefficient. This makes percentile response times and business throughput more informative than a single average. Hardware still matters, but it cannot remove serialization caused by locks or compensate indefinitely for requests that consume unnecessary service time.
Choose hierarchy representations according to the walk and computation required
There is no universal tree model. An adjacency model stores each node's parent and works naturally with recursive traversal features. A materialized path stores the ancestor route with each node, making some descendant and ancestor searches convenient. A nested set model assigns enclosing left and right values to nodes, which can make subtree retrieval direct but requires renumbering many values when the structure changes.
Match the representation to the question. Finding a file's location walks upward; listing everything beneath a manager walks downward; calculating ingredient proportions through nested recipes requires values to propagate across levels. The book's tests on an approximately 800-row example found adjacency traversal effective, while a recursive common table expression was better suited to propagating calculations. Tree walking is inherently sequential in many implementations, so large hierarchies require realistic scale tests.
Hierarchy design exposes a broader database truth: representation determines which questions are cheap. The same data can be organized for traversal, update simplicity, or aggregation, but rarely optimizes all three equally. This is similar to choosing a graph representation in algorithms, where an adjacency list and a matrix privilege different operations. The book's warning against a universal answer is well judged. One additional constraint is governance: if a hierarchy changes frequently, the operational burden of maintaining stored paths or nested intervals may outweigh faster reads.
Judge SQL by useful business throughput, not by tidy execution plans
Measure meaningful work. Monitoring should connect statements to the application modules that issued them and to business events such as orders or claims. A database query count far higher than the number of business items being processed can expose repeated, unnecessary work. Page visits, parsing costs, rows affected, and elapsed time help explain the load, but none matters without knowing what useful work the system completed.
Plans are evidence, not scores. In one example, an execution plan made almost entirely of index accesses took 72 seconds, while a plan with full scans took 27 seconds. The fastest plan in another version took one second. For flexible search screens, dynamically building only the joins needed for supplied criteria can also help; parameter binding preserves reuse and blocks SQL injection. Measure the result rather than rewarding a plan's appearance.
The book's strongest operational insight is that performance must be accounted for in units the organization values. This resembles activity-based costing: technical effort is meaningful only when related to the service it enables. Execution plans remain valuable diagnostic artifacts, but their operators are not interchangeable units of cost across different data distributions and hardware. The approach also resists a common incentive problem, where teams optimize a visible query while ignoring thousands of repetitive calls. The best metric combines user experience, business throughput, and the resources consumed to deliver it.
Analysis
The book's central argument is that SQL performance is a system property. It emerges from the interaction of business semantics, schema design, query structure, data distribution, physical storage, concurrency, and operational measurement. That is why the most convincing examples are not demonstrations of obscure syntax. They show how reframing work, such as replacing repeated queries with one set operation, can deliver orders-of-magnitude gains while conventional tuning merely polishes the wrong design.
Its most useful intellectual tool is the distinction between relational work and presentation work. Filtering and joining can often be transformed safely by an optimizer; sorting, ranking, and limiting impose sequence and narrow those options. This helps explain both why SQL is declarative in principle and why its practical performance depends on how a statement is expressed. The model is valuable, though not a complete account of every modern optimizer, which may push predicates, rewrite subqueries, or exploit features that differ by product and version.
The book's strategic analogies support a practical discipline: identify the business objective, understand the data, choose an access path, and test under real conditions. Its repeated insistence on workload evidence is more enduring than product-specific prescriptions. Many examples come from 2006-era systems and hardware, so exact timings, particular database features, and some tuning assumptions should not be transferred uncritically to current platforms. The broader method remains sound: benchmark the actual engine, data shape, and concurrency level.
A further strength is its refusal to turn good principles into slogans. Normalize for integrity, but recognize the read-oriented case for dimensional models. Use indexes, but account for write costs. Prefer set operations, but respect recovery and lock duration. The resulting view is less a recipe book than a way to ask better engineering questions. It treats performance as the pursuit of useful work per unit of shared capacity, not as a contest to make one query look elegant or one execution plan look familiar.
Review Summary
The Art of SQL receives mostly positive reviews, with readers praising its advanced content and unique presentation style inspired by Sun Tzu's Art of War. Experienced SQL users find it enlightening and valuable for improving database performance. The book is praised for its depth, organization, and engaging writing style. However, some readers find it challenging or disjointed. Many reviewers recommend it for SQL experts looking to enhance their skills, while cautioning that it may be too advanced for beginners.
Glossary
relational layer
Set-based query coreFaroult's term for the portion of SQL work that uses relational operations to identify and combine rows, such as filtering and joining. The optimizer can often transform this work while preserving the result. Operations such as ordering, ranking, and limiting impose presentation or finite-set requirements, reducing the optimizer's freedom.
data-driven partitioning
Partitions chosen by data valuesA physical organization in which values in one or more columns determine the partition that stores each row. Range partitioning can group time periods, while hash partitioning spreads keys among partitions. The database can sometimes skip irrelevant partitions, but the chosen key must reflect the workload and avoid harmful skew or write hotspots.
star transformation
Dimension filters intersect fact rowsAn optimization for star-schema queries that applies separate dimension filters to identify matching fact-table row identifiers, then combines those identifiers before retrieving the fact rows. Bitmap indexes can make the intersections especially efficient in read-oriented systems. The approach depends on suitable indexing and is generally a poor fit for frequently updated transactional tables.
adjacency model
Each node stores its parentA hierarchy representation that stores a parent identifier alongside each node. It is simple to maintain and can support recursive traversal through database features such as Oracle's CONNECT BY or recursive common table expressions. Queries that need many descendants or repeated aggregate calculations may require repeated traversal, so performance depends on the task and implementation.
materialized path model
Each node stores its ancestryA hierarchy representation that records a node's position as a path containing the identifiers of its ancestors. Prefix comparisons can identify related nodes, and the path can provide an ordering for traversal. The path should not be mistaken for the node's business identity, and changing a node's place may require updating stored paths.
nested set model
Intervals encode tree ancestryA hierarchy representation that assigns each node left and right values so that a parent's interval encloses the intervals of its descendants. A range test can retrieve a subtree without recursively following each parent link. Insertions or structural changes can require renumbering many nodes, making this representation costly to maintain in changing hierarchies.
pivot table
Utility numbers for row expansionIn Faroult's usage, a table containing a sequence of numbers that can be joined to another table to repeat rows a controlled number of times. SQL can then use each number to split a delimited string, generate a sequence, or reshape data. It is a practical workaround for data stored in an awkward format, not a substitute for a sound schema.
bind variables
Parameters supplied separatelyParameter markers in a SQL statement whose actual values are supplied separately at execution time. Reusing the same statement pattern can reduce repeated parsing and optimization work. Binding also prevents user-provided text from becoming executable SQL, reducing SQL injection risk. It is especially useful when search values vary but the query structure remains similar.
nine situations
Common query-pattern taxonomyThe book's organizing set of recurring SQL problems, including selective searches, broad criteria whose intersection is small, large result sets, self-joins, aggregation, date searches, and searches for absent data. The categories help a developer identify which filtering, indexing, and query-shaping questions matter before choosing a SQL expression.
Download PDF
Download EPUB
.epub digital book format is ideal for reading ebooks on phones, tablets, and e-readers.