Case studies

This section presents three real-world case studies using the book inventory and order processing database featured throughout this chapter. A review of the case studies will demonstrate how the strategies and techniques presented in this chapter can be used to solve common database performance problems.

Each case study is presented in the following format:

• The scenario
• The initial SQL query
• The problem
• The optimization steps
• The result

Case study 1

Scenario: Every time the bookstore’s administrator runs the sales report, it takes several minutes – much longer than it should. The report simply summarizes total sales by title. The database schema is the same as the one presented earlier in this chapter.

Initial SQL query:

SELECT b.Title, SUM(o.Qty * o.Price) AS TotalSales
FROM Orders o
JOIN Books b ON o.BookID = b.BookID
GROUP BY b.Title;

Problem: The query performs a full table scan of both the Books and Orders...