73 ) Sql Query optimization steps

 

Sample Improperly Constructed Complex Query (Anti-Pattern Example)

SQL

SELECT 
    DISTINCT
    UPPER(r.RegionName) AS RegionName,
    s.StoreName,
    YEAR(o.OrderDate) AS OrderYear,
    MONTH(o.OrderDate) AS OrderMonth,
    (SELECT COUNT(OrderID) FROM Sales.Orders WHERE StoreID = s.StoreID) AS TotalOrdersCount,
    (SELECT SUM(Quantity * UnitPrice * (1 - Discount)) FROM Sales.OrderDetails WHERE OrderID IN (SELECT OrderID FROM Sales.Orders WHERE StoreID = s.StoreID)) AS TotalStoreRevenue
FROM 
    Sales.Orders o
JOIN 
    Sales.OrderDetails od ON o.OrderID = od.OrderID
JOIN 
    Sales.Stores s ON o.StoreID = s.StoreID
JOIN 
    Sales.Regions r ON s.RegionID = r.RegionID
WHERE 
    r.RegionName LIKE '%North%'
    AND YEAR(o.OrderDate) = 2025
    AND MONTH(o.OrderDate) = 01
    AND o.StoreID IN (SELECT StoreID FROM Sales.Stores WHERE ActiveStatus = 1);

Step-by-Step Construction with Baseline and Final Runtime Captured

Step 0: Capture Baseline Runtime and Profile

• What we do: Enable profiling right at the start to measure the unoptimized query's exact execution runtime.

• The Query at this Step:

  SQL

  SET profiling = 1;
  
  SELECT 
      DISTINCT
      UPPER(r.RegionName) AS RegionName,
      s.StoreName,
      YEAR(o.OrderDate) AS OrderYear,
      MONTH(o.OrderDate) AS OrderMonth,
      (SELECT COUNT(OrderID) FROM Sales.Orders WHERE StoreID = s.StoreID) AS TotalOrdersCount,
      (SELECT SUM(Quantity * UnitPrice * (1 - Discount)) FROM Sales.OrderDetails WHERE OrderID IN (SELECT OrderID FROM Sales.Orders WHERE StoreID = s.StoreID)) AS TotalStoreRevenue
  FROM 
      Sales.Orders o
  JOIN 
      Sales.OrderDetails od ON o.OrderID = od.OrderID
  JOIN 
      Sales.Stores s ON o.StoreID = s.StoreID
  JOIN 
      Sales.Regions r ON s.RegionID = r.RegionID
  WHERE 
      r.RegionName LIKE '%North%'
      AND YEAR(o.OrderDate) = 2025
      AND MONTH(o.OrderDate) = 01
      AND o.StoreID IN (SELECT StoreID FROM Sales.Stores WHERE ActiveStatus = 1);
  
  SHOW PROFILES;
  

• Sample Baseline Runtime Output: 8.41250000 seconds

Step 1: Remove Correlated Scalar Subqueries and Move Logic to Aggregated Joins

• What we change: Eliminate scalar subqueries inside the SELECT clause and establish a baseline CTE (FilteredOrders).

• The Query at this Step:

  SQL

  WITH FilteredOrders AS (
      SELECT 
          o.OrderID,
          o.StoreID,
          YEAR(o.OrderDate) AS OrderYear,
          MONTH(o.OrderDate) AS OrderMonth
      FROM Sales.Orders o
      INNER JOIN Sales.Stores s ON o.StoreID = s.StoreID
      INNER JOIN Sales.Regions r ON s.RegionID = r.RegionID
      WHERE r.RegionName LIKE '%North%'
        AND YEAR(o.OrderDate) = 2025
        AND MONTH(o.OrderDate) = 01
        AND s.ActiveStatus = 1
  )
  SELECT * FROM FilteredOrders;
  

Step 2: Fix Non-SARGable Filters and Leading Wildcards

• What we change: Replace the leading wildcard text search (LIKE '%North%') with an exact match, and swap function-wrapped date filters (YEAR() / MONTH()) for a deterministic range.

• The Query at this Step:

  SQL

  WITH FilteredOrders AS (
      SELECT 
          o.OrderID,
          o.StoreID,
          YEAR(o.OrderDate) AS OrderYear,
          MONTH(o.OrderDate) AS OrderMonth
      FROM Sales.Orders o
      INNER JOIN Sales.Stores s ON o.StoreID = s.StoreID
      INNER JOIN Sales.Regions r ON s.RegionID = r.RegionID
      WHERE r.RegionName = 'North America'
        AND s.ActiveStatus = 1
        AND o.OrderDate >= '2025-01-01' 
        AND o.OrderDate < '2025-02-01'
  )
  SELECT * FROM FilteredOrders;
  

Step 3: Implement Grouped Aggregations Instead of Nested Subqueries

• What we change: Introduce a second CTE (AggregatedSales) to compute metrics using proper grouping and joins against OrderDetails.

• The Query at this Step:

  SQL

  WITH FilteredOrders AS (
      SELECT 
          o.OrderID,
          o.StoreID,
          YEAR(o.OrderDate) AS OrderYear,
          MONTH(o.OrderDate) AS OrderMonth
      FROM Sales.Orders o
      INNER JOIN Sales.Stores s ON o.StoreID = s.StoreID
      INNER JOIN Sales.Regions r ON s.RegionID = r.RegionID
      WHERE r.RegionName = 'North America'
        AND s.ActiveStatus = 1
        AND o.OrderDate >= '2025-01-01' 
        AND o.OrderDate < '2025-02-01'
  ),
  AggregatedSales AS (
      SELECT 
          fo.OrderYear,
          fo.OrderMonth,
          fo.StoreID,
          COUNT(DISTINCT fo.OrderID) AS TotalOrdersCount,
          SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)) AS TotalStoreRevenue
      FROM FilteredOrders fo
      INNER JOIN Sales.OrderDetails od ON fo.OrderID = od.OrderID
      GROUP BY 
          fo.OrderYear,
          fo.OrderMonth,
          fo.StoreID
  )
  SELECT * FROM AggregatedSales;
  

Step 4: Final Relational Projection, Execution, and Final Runtime Capture

• What we change: Map aggregated results back to descriptive store and region attributes, remove the redundant DISTINCT keyword, and capture the final runtime execution profile.

• The Fully Optimized Query & Execution Script:

  SQL

  SET profiling = 1;
  
  WITH FilteredOrders AS (
      SELECT 
          o.OrderID,
          o.StoreID,
          YEAR(o.OrderDate) AS OrderYear,
          MONTH(o.OrderDate) AS OrderMonth
      FROM Sales.Orders o
      INNER JOIN Sales.Stores s ON o.StoreID = s.StoreID
      INNER JOIN Sales.Regions r ON s.RegionID = r.RegionID
      WHERE r.RegionName = 'North America'
        AND s.ActiveStatus = 1
        AND o.OrderDate >= '2025-01-01' 
        AND o.OrderDate < '2025-02-01'
  ),
  AggregatedSales AS (
      SELECT 
          fo.OrderYear,
          fo.OrderMonth,
          fo.StoreID,
          COUNT(DISTINCT fo.OrderID) AS TotalOrdersCount,
          SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)) AS TotalStoreRevenue
      FROM FilteredOrders fo
      INNER JOIN Sales.OrderDetails od ON fo.OrderID = od.OrderID
      GROUP BY 
          fo.OrderYear,
          fo.OrderMonth,
          fo.StoreID
  )
  SELECT 
      UPPER(r.RegionName) AS RegionName,
      s.StoreName,
      a.OrderYear,
      a.OrderMonth,
      a.TotalOrdersCount,
      a.TotalStoreRevenue
  FROM AggregatedSales a
  INNER JOIN Sales.Stores s ON a.StoreID = s.StoreID
  INNER JOIN Sales.Regions r ON s.RegionID = r.RegionID;
  
  SHOW PROFILES;
  

• 
  

• 
  

• 
  

• Sample Final Runtime Output: 0.12400000 seconds (Improved from 8.41 seconds down to 0.12 seconds)