Optimizing a Complex SQL Query: A Business Case for Performance Improvement

In this article, we’ll break down a complex SQL query, explain its business use case, and demonstrate how performance improvements were applied to make it run faster. SQL optimization is an essential skill in database management, especially when dealing with large datasets that impact the performance of applications and reporting systems. We’ll focus on an example of an SQL query in the context of an e-commerce platform that analyzes sales data.

Business Case

Imagine an e-commerce company needs to analyze customer behavior based on historical sales data. The company wants to identify which products are the most popular in each region and during specific time periods. Additionally, the company must calculate the total sales, average order value, and customer retention rates over the past quarter for various marketing campaigns.

To achieve this, a complex SQL query has been created. This query joins multiple tables, applies filtering, grouping, and aggregation, and returns a large dataset that includes sales totals, product categories, and regional data.

The Initial Query

Let’s start with the initial, unoptimized query. This query selects the total sales, product category, and region data for products sold in the last three months, grouped by product and region.

SELECT       p.product_id,      p.product_name,      c.category_name,      r.region_name,      SUM(s.sales_amount) AS total_sales,      AVG(s.sales_amount) AS avg_sales,      COUNT(DISTINCT o.customer_id) AS unique_customers  FROM       sales s  JOIN       products p ON s.product_id = p.product_id  JOIN       categories c ON p.category_id = c.category_id  JOIN       regions r ON s.region_id = r.region_id  JOIN       orders o ON s.order_id = o.order_id  WHERE       s.sales_date >= CURDATE() - INTERVAL 3 MONTH  GROUP BY       p.product_id, c.category_name, r.region_name  ORDER BY       total_sales DESC;

Understanding the Query

1. Sales Data Join: This query retrieves sales data by joining the sales, products, categories, regions, and orders tables.
2. Filters: The filter s.sales_date >= CURDATE() - INTERVAL 3 MONTH ensures that only sales from the past three months are considered.
3. Aggregations: We calculate the total sales (SUM(s.sales_amount)), the average sales (AVG(s.sales_amount)), and the number of unique customers (COUNT(DISTINCT o.customer_id)).
4. Grouping and Sorting: The query groups the data by product_id, category_name, and region_name, and orders the results by total_sales DESC to identify the top-performing products.

Performance Issues in the Initial Query

This query works fine for smaller datasets, but as the company’s sales grow, performance becomes an issue. The query runs slowly for several reasons:

• Joins on large tables: The sales, products, categories, regions, and orders tables are large and involve multiple joins, which increases the computational complexity.
• Aggregation over large datasets: The SUM(), AVG(), and COUNT(DISTINCT) functions perform aggregations on potentially millions of rows.
• Lack of Indexes: The query may not benefit from existing indexes, especially when filtering by sales_date or joining on foreign keys.

Performance Optimization Strategies

To improve the query performance, we can apply several strategies. These include proper indexing, query refactoring, and leveraging SQL optimization techniques.

1. Indexing

Indexing is one of the most effective ways to speed up SQL queries. We’ll add indexes on the columns frequently used in WHERE, JOIN, and GROUP BY clauses.

Adding Indexes

-- Index on sales_date for faster filtering  CREATE INDEX idx_sales_date ON sales(sales_date);    -- Index on product_id, category_id, and region_id for faster joins  CREATE INDEX idx_sales_product ON sales(product_id);  CREATE INDEX idx_sales_region ON sales(region_id);  CREATE INDEX idx_products_category ON products(category_id);    -- Index on order_id for faster joining with orders  CREATE INDEX idx_sales_order ON sales(order_id);

2. Using Subqueries or CTEs for Pre-Aggregation

Instead of performing aggregations across the entire dataset in one step, we can pre-aggregate the data and reduce the number of rows that need to be processed.

Refactored Query with CTE (Common Table Expression)

We will first calculate the total sales, average sales, and unique customers in a CTE, and then join it with the other tables.

WITH sales_summary AS (      SELECT           s.product_id,          s.region_id,          SUM(s.sales_amount) AS total_sales,          AVG(s.sales_amount) AS avg_sales,          COUNT(DISTINCT o.customer_id) AS unique_customers      FROM           sales s      JOIN           orders o ON s.order_id = o.order_id      WHERE           s.sales_date >= CURDATE() - INTERVAL 3 MONTH      GROUP BY           s.product_id, s.region_id  )  SELECT       p.product_id,      p.product_name,      c.category_name,      r.region_name,      ss.total_sales,      ss.avg_sales,      ss.unique_customers  FROM       sales_summary ss  JOIN       products p ON ss.product_id = p.product_id  JOIN       categories c ON p.category_id = c.category_id  JOIN       regions r ON ss.region_id = r.region_id  ORDER BY       ss.total_sales DESC;

Benefits of this approach:

• Reduced dataset size: The CTE computes the aggregations on a smaller subset of data, reducing the complexity in the final JOIN.
• Simplified Execution Plan: By pre-aggregating data in the sales_summary, the database can optimize the final query more effectively.

3. Query Partitioning

If the dataset is extremely large, partitioning can help. Partitioning divides the data into smaller, more manageable chunks. For example, partitioning the sales table by sales_date can significantly improve query performance by limiting the amount of data processed.

-- Partition sales table by sales_date (if using a database that supports partitioning)  CREATE TABLE sales (      sales_id INT PRIMARY KEY,      product_id INT,      order_id INT,      region_id INT,      sales_date DATE,      sales_amount DECIMAL(10, 2)  )  PARTITION BY RANGE (YEAR(sales_date)) (      PARTITION p2019 VALUES LESS THAN (2020),      PARTITION p2020 VALUES LESS THAN (2021),      PARTITION p2021 VALUES LESS THAN (2022),      PARTITION p2022 VALUES LESS THAN (2023)  );

Benefits of partitioning:

• Faster Data Access: Partitioning allows the query planner to read only the relevant partitions, reducing the I/O overhead.
• Efficient Aggregation: Aggregations on partitioned tables are more efficient since the database scans fewer rows.

4. Use of Materialized Views

In cases where the query needs to be executed frequently, materialized views can store the precomputed result set, which can be refreshed periodically. This eliminates the need to recompute aggregates every time the query is run.

-- Create a materialized view for the sales summary  CREATE MATERIALIZED VIEW sales_summary_view AS  SELECT       s.product_id,      s.region_id,      SUM(s.sales_amount) AS total_sales,      AVG(s.sales_amount) AS avg_sales,      COUNT(DISTINCT o.customer_id) AS unique_customers  FROM       sales s  JOIN       orders o ON s.order_id = o.order_id  WHERE       s.sales_date >= CURDATE() - INTERVAL 3 MONTH  GROUP BY       s.product_id, s.region_id;    -- Query the materialized view for faster results  SELECT       p.product_id,      p.product_name,      c.category_name,      r.region_name,      ss.total_sales,      ss.avg_sales,      ss.unique_customers  FROM       sales_summary_view ss  JOIN       products p ON ss.product_id = p.product_id  JOIN       categories c ON p.category_id = c.category_id  JOIN       regions r ON ss.region_id = r.region_id  ORDER BY       ss.total_sales DESC;

EXPLAIN SELECT       p.product_id,      p.product_name,      c.category_name,      r.region_name,      SUM(s.sales_amount) AS total_sales,      AVG(s.sales_amount) AS avg_sales,      COUNT(DISTINCT o.customer_id) AS unique_customers  FROM       sales s  JOIN       products p ON s.product_id = p.product_id  JOIN       categories c ON p.category_id = c.category_id  JOIN       regions r ON s.region_id = r.region_id  JOIN       orders o ON s.order_id = o.order_id  WHERE       s.sales_date >= CURDATE() - INTERVAL 3 MONTH  GROUP BY       p.product_id, c.category_name, r.region_name  ORDER BY       total_sales DESC;