Optimizing Query Performance for Expensive Table Spool in SQL Server: A Step-by-Step Guide

Optimizing Query Performance for Expensive Table Spool

In this article, we’ll explore ways to improve query performance for expensive table spool in SQL Server. The scenario provided involves a complex query that joins multiple tables and uses various indexing techniques to retrieve data from a single stage table.

Understanding the Problem

The given query takes a significant amount of time to execute, with an estimated operated cost of 12.22%. The execution plan reveals that there is a table spool involved, which accounts for 50% of the total cost. This indicates that the query is spending a substantial amount of time on the table spool operation.

Query Analysis

The provided query involves several steps:

Table Creation: Two temporary tables, #Enrollment_grp and #tmp, are created.
Index Creation: Indexes are created on both tables to improve query performance.
Data Insertion: Data is inserted into the temporary tables based on specific conditions.
Join Operation: The two temporary tables are joined together using a complex join condition.

Optimizing the Query

To optimize the query, we can consider several techniques:

1. Using EXISTS Instead of IN

The original query uses the IN operator to filter data based on specific conditions. However, this can lead to unnecessary joins and reduce performance. We can replace the IN operator with an EXISTS clause, which is more efficient.

-- Before
AND stg.CustomerID IN (SELECT CustomerID FROM #Enrollment_grp WHERE EWSPurchaseDate &lt;= '2018-10-31' AND TerminationDate &gt;= '2018-10-01' GROUP BY CustomerID HAVING COUNT(DISTINCT EWSPlanType) = 3)
AND stg.ProductSKU IN (SELECT ProductSKU FROM #Enrollment_grp WHERE EWSPurchaseDate &lt;= '2018-10-31' AND TerminationDate &gt;= '2018-10-01' GROUP BY ProductSKU HAVING COUNT(DISTINCT EWSPlanType) = 3)
AND stg.StoreID    IN (SELECT StoreID FROM #Enrollment_grp WHERE EWSPurchaseDate &lt;= '2018-10-31' AND TerminationDate &gt;= '2018-10-01' GROUP BY StoreID HAVING COUNT(DISTINCT EWSPlanType) = 3)

-- After
AND EXISTS (SELECT 1 FROM #Enrollment_grp E WHERE STG.CustomerID = E.CustomerID AND STG.ProductSKU = E.ProductSKU AND STG.StoreID = E.StoreID)

2. Using Temporary Table with Index

To further improve performance, we can create a temporary table with an index on the join column.

-- Before
INSERT INTO #tmp (TransactionNo,
StoreName,
EWSPlan,
ProductType,
PlanStartDate,
PlanEndDate)
    (SELECT DISTINCT
        stg.CustomerID AS TransactionNo
       ,stg.StoreID AS StoreName
       ,CASE
            WHEN SUM(DISTINCT CAST(stg.ClientEWSWarrantySKU AS INT)) = 6 THEN 'AA'
            WHEN SUM(DISTINCT CAST(stg.ClientEWSWarrantySKU AS INT)) = 15 THEN 'BB'
            WHEN SUM(DISTINCT CAST(stg.ClientEWSWarrantySKU AS INT)) = 33 THEN 'CC'
            WHEN SUM(DISTINCT CAST(stg.ClientEWSWarrantySKU AS INT)) = 42 THEN 'DD'
        END AS EWSPlan
       ,stg.ProductSKU AS ProductType
       ,CAST(FORMAT(stg.EWSPurchaseDate, 'yyyy/MM/dd') AS VARCHAR) AS PlanStartDate
       ,'' AS PlanEndDate--,
    FROM STAGE_ENROLLMENT AS stg (NOLOCK)
    WHERE stg.ClientID = 1
    AND stg.EWSPurchaseDate &lt;= '2018-10-31'
    AND stg.TerminationDate &gt;= '2018-10-01'
    AND stg.ContractStatus = 1
    GROUP BY stg.CustomerID,stg.EWSPurchaseDate,stg.StoreID,stg.ProductSKU HAVING COUNT(DISTINCT stg.EWSPlanType) = 3
    AND (CASE
        WHEN SUM(DISTINCT CAST(stg.ClientEWSWarrantySKU AS INT)) = 6 THEN 'AA'
        WHEN SUM(DISTINCT CAST(stg.ClientEWSWarrantySKU AS INT)) = 15 THEN 'BB'
        WHEN SUM(DISTINCT CAST(stg.ClientEWSWarrantySKU AS INT)) = 33 THEN 'CC'
        WHEN SUM(DISTINCT CAST(stg.ClientEWSWarrantySKU AS INT)) = 42 THEN 'DD'
    END) IS NOT NULL

-- After
CREATE TABLE #tmpTemp (
    CustomerID NVARCHAR(100),
    ProductSKU NVARCHAR(200),
    StoreID NVARCHAR(100)
)

INSERT INTO #tmpTemp (CustomerID,ProductSKU,StoreID)
SELECT stg.CustomerID, stg.ProductSKU, stg.StoreID
FROM STAGE_ENROLLMENT AS stg (NOLOCK)
WHERE stg.ClientID = 1
AND stg.EWSPurchaseDate &lt;= '2018-10-31'
AND stg.TerminationDate &gt;= '2018-10-01'
AND stg.ContractStatus = 1

CREATE INDEX IX_tmpTemp_CustomerID ON #tmpTemp(CustomerID)
CREATE INDEX IX_tmpTemp_ProductSKU ON #tmpTemp(ProductSKU)
CREATE INDEX IX_tmpTemp_StoreID ON #tmpTemp(StoreID)

INSERT INTO #tmp (TransactionNo,
StoreName,
EWSPlan,
ProductType,
PlanStartDate,
PlanEndDate)
    SELECT DISTINCT
        stg.CustomerID AS TransactionNo
       ,stg.StoreID AS StoreName
       ,CASE
            WHEN SUM(DISTINCT CAST(stg.ClientEWSWarrantySKU AS INT)) = 6 THEN 'AA'
            WHEN SUM(DISTINCT CAST(stg.ClientEWSWarrantySKU AS INT)) = 15 THEN 'BB'
            WHEN SUM(DISTINCT CAST(stg.ClientEWSWarrantySKU AS INT)) = 33 THEN 'CC'
            WHEN SUM(DISTINCT CAST(stg.ClientEWSWarrantySKU AS INT)) = 42 THEN 'DD'
        END AS EWSPlan
       ,stg.ProductSKU AS ProductType
       ,CAST(FORMAT(stg.EWSPurchaseDate, 'yyyy/MM/dd') AS VARCHAR) AS PlanStartDate
       ,'' AS PlanEndDate--,
    FROM #tmpTemp t
    WHERE EXISTS (SELECT 1 FROM STAGE_ENROLLMENT AS stg (NOLOCK)
                WHERE stg.CustomerID = t.CustomerID AND stg.ProductSKU = t.ProductSKU AND stg.StoreID = t.StoreID)

3. Reordering Operations

We can reorder the operations in the query to improve performance.

-- Before
INSERT INTO #tmp (TransactionNo,
StoreName,
EWSPlan,
ProductType,
PlanStartDate,
PlanEndDate)
    SELECT DISTINCT
        stg.CustomerID AS TransactionNo
       ,stg.StoreID AS StoreName
       ,CASE
            WHEN SUM(DISTINCT CAST(stg.ClientEWSWarrantySKU AS INT)) = 6 THEN 'AA'
            WHEN SUM(DISTINCT CAST(stg.ClientEWSWarrantySKU AS INT)) = 15 THEN 'BB'
            WHEN SUM(DISTINCT CAST(stg.ClientEWSWarrantySKU AS INT)) = 33 THEN 'CC'
            WHEN SUM(DISTINCT CAST(stg.ClientEWSWarrantySKU AS INT)) = 42 THEN 'DD'
        END AS EWSPlan
       ,stg.ProductSKU AS ProductType
       ,CAST(FORMAT(stg.EWSPurchaseDate, 'yyyy/MM/dd') AS VARCHAR) AS PlanStartDate
       ,'' AS PlanEndDate--,
    FROM STAGE_ENROLLMENT AS stg (NOLOCK)
    WHERE stg.ClientID = 1
    AND stg.EWSPurchaseDate &lt;= '2018-10-31'
    AND stg.TerminationDate &gt;= '2018-10-01'
    AND stg.ContractStatus = 1
    GROUP BY stg.CustomerID,stg.EWSPurchaseDate,stg.StoreID,stg.ProductSKU HAVING COUNT(DISTINCT stg.EWSPlanType) = 3
    AND (CASE
        WHEN SUM(DISTINCT CAST(stg.ClientEWSWarrantySKU AS INT)) = 6 THEN 'AA'
        WHEN SUM(DISTINCT CAST(stg.ClientEWSWarrantySKU AS INT)) = 15 THEN 'BB'
        WHEN SUM(DISTINCT CAST(stg.ClientEWSWarrantySKU AS INT)) = 33 THEN 'CC'
        WHEN SUM(DISTINCT CAST(stg.ClientEWSWarrantySKU AS INT)) = 42 THEN 'DD'
    END) IS NOT NULL

-- After
INSERT INTO #tmp (TransactionNo,
StoreName,
EWSPlan,
ProductType,
PlanStartDate,
PlanEndDate)
SELECT DISTINCT
    stg.CustomerID AS TransactionNo
   ,stg.StoreID AS StoreName
   ,CASE
        WHEN SUM(DISTINCT CAST(stg.ClientEWSWarrantySKU AS INT)) = 6 THEN 'AA'
        WHEN SUM(DISTINCT CAST(stg.ClientEWSWarrantySKU AS INT)) = 15 THEN 'BB'
        WHEN SUM(DISTINCT CAST(stg.ClientEWSWarrantySKU AS INT)) = 33 THEN 'CC'
        WHEN SUM(DISTINCT CAST(stg.ClientEWSWarrantySKU AS INT)) = 42 THEN 'DD'
    END AS EWSPlan
   ,stg.ProductSKU AS ProductType
   ,CAST(FORMAT(stg.EWSPurchaseDate, 'yyyy/MM/dd') AS VARCHAR) AS PlanStartDate
   ,'' AS PlanEndDate--,
FROM STAGE_ENROLLMENT AS stg (NOLOCK)
WHERE stg.ClientID = 1
AND stg.EWSPurchaseDate &lt;= '2018-10-31'
AND stg.TerminationDate &gt;= '2018-10-01'
AND stg.ContractStatus = 1

INSERT INTO #tmpTemp (CustomerID,ProductSKU,StoreID)
SELECT stg.CustomerID, stg.ProductSKU, stg.StoreID
FROM STAGE_ENROLLMENT AS stg (NOLOCK)
WHERE EXISTS (
    SELECT 1
    FROM #tmp t
    WHERE t.TransactionNo = stg.CustomerID AND t.StoreName = stg.StoreID
)

CREATE INDEX IX_tmpTemp_CustomerID ON #tmpTemp(CustomerID)
CREATE INDEX IX_tmpTemp_ProductSKU ON #tmpTemp(ProductSKU)
CREATE INDEX IX_tmpTemp_StoreID ON #tmpTemp(StoreID)

INSERT INTO #tmp (TransactionNo,
StoreName,
EWSPlan,
ProductType,
PlanStartDate,
PlanEndDate)
SELECT DISTINCT
    stg.CustomerID AS TransactionNo
   ,stg.StoreID AS StoreName
   ,CASE
        WHEN SUM(DISTINCT CAST(stg.ClientEWSWarrantySKU AS INT)) = 6 THEN 'AA'
        WHEN SUM(DISTINCT CAST(stg.ClientEWSWarrantySKU AS INT)) = 15 THEN 'BB'
        WHEN SUM(DISTINCT CAST(stg.ClientEWSWarrantySKU AS INT)) = 33 THEN 'CC'
        WHEN SUM(DISTINCT CAST(stg.ClientEWSWarrantySKU AS INT)) = 42 THEN 'DD'
    END AS EWSPlan
   ,stg.ProductSKU AS ProductType
   ,CAST(FORMAT(stg.EWSPurchaseDate, 'yyyy/MM/dd') AS VARCHAR) AS PlanStartDate
   ,'' AS PlanEndDate--,
FROM #tmpTemp t
WHERE EXISTS (SELECT 1 FROM STAGE_ENROLLMENT AS stg (NOLOCK)
             WHERE stg.CustomerID = t.CustomerID AND stg.ProductSKU = t.ProductSKU AND stg.StoreID = t.StoreID)

4. Using Table Valued Functions

We can create a table-valued function to simplify the query and improve performance.

-- Before
INSERT INTO #tmp (TransactionNo,
StoreName,
EWSPlan,
ProductType,
PlanStartDate,
PlanEndDate)
    SELECT DISTINCT
        stg.CustomerID AS TransactionNo
       ,stg.StoreID AS StoreName
       ,CASE
            WHEN SUM(DISTINCT CAST(stg.ClientEWSWarrantySKU AS INT)) = 6 THEN 'AA'
            WHEN SUM(DISTINCT CAST(stg.ClientEWSWarrantySKU AS INT)) = 15 THEN 'BB'
            WHEN SUM(DISTINCT CAST(stg.ClientEWSWarrantySKU AS INT)) = 33 THEN 'CC'
            WHEN SUM(DISTINCT CAST(stg.ClientEWSWarrantySKU AS INT)) = 42 THEN 'DD'
        END AS EWSPlan
       ,stg.ProductSKU AS ProductType
       ,CAST(FORMAT(stg.EWSPurchaseDate, 'yyyy/MM/dd') AS VARCHAR) AS PlanStartDate
       ,'' AS PlanEndDate--,
    FROM STAGE_ENROLLMENT AS stg (NOLOCK)
    WHERE stg.ClientID = 1
    AND stg.EWSPurchaseDate &lt;= '2018-10-31'
    AND stg.TerminationDate &gt;= '2018-10-01'
    AND stg.ContractStatus = 1
    GROUP BY stg.CustomerID,stg.EWSPurchaseDate,stg.StoreID,stg.ProductSKU HAVING COUNT(DISTINCT stg.EWSPlanType) = 3
    AND (CASE
        WHEN SUM(DISTINCT CAST(stg.ClientEWSWarrantySKU AS INT)) = 6 THEN 'AA'
        WHEN SUM(DISTINCT CAST(stg.ClientEWSWarrantySKU AS INT)) = 15 THEN 'BB'
        WHEN SUM(DISTINCT CAST(stg.ClientEWSWarrantySKU AS INT)) = 33 THEN 'CC'
        WHEN SUM(DISTINCT CAST(stg.ClientEWSWarrantySKU AS INT)) = 42 THEN 'DD'
    END) IS NOT NULL

-- After
CREATE FUNCTION GetData (@CustomerID NVARCHAR(100), @StoreID NVARCHAR(100))
RETURNS TABLE
AS
RETURN
(
    SELECT
        stg.CustomerID AS TransactionNo
       ,stg.StoreID AS StoreName
       ,CASE
            WHEN SUM(DISTINCT CAST(stg.ClientEWSWarrantySKU AS INT)) = 6 THEN 'AA'
            WHEN SUM(DISTINCT CAST(stg.ClientEWSWarrantySKU AS INT)) = 15 THEN 'BB'
            WHEN SUM(DISTINCT CAST(stg.ClientEWSWarrantySKU AS INT)) = 33 THEN 'CC'
            WHEN SUM(DISTINCT CAST(stg.ClientEWSWarrantySKU AS INT)) = 42 THEN 'DD'
        END AS EWSPlan
       ,stg.ProductSKU AS ProductType
       ,CAST(FORMAT(stg.EWSPurchaseDate, 'yyyy/MM/dd') AS VARCHAR) AS PlanStartDate
       ,'' AS PlanEndDate--,
    FROM STAGE_ENROLLMENT AS stg (NOLOCK)
    WHERE stg.ClientID = @CustomerID AND stg.StoreID = @StoreID
    AND stg.EWSPurchaseDate &lt;= '2018-10-31'
    AND stg.TerminationDate &gt;= '2018-10-01'
    AND stg.ContractStatus = 1

INSERT INTO #tmp (TransactionNo,
StoreName,
EWSPlan,
ProductType,
PlanStartDate,
PlanEndDate)
SELECT DISTINCT
    stg.CustomerID AS TransactionNo
   ,stg.StoreID AS StoreName
   ,CASE
        WHEN SUM(DISTINCT CAST(stg.ClientEWSWarrantySKU AS INT)) = 6 THEN 'AA'
        WHEN SUM(DISTINCT CAST(stg.ClientEWSWarrantySKU AS INT)) = 15 THEN 'BB'
        WHEN SUM(DISTINCT CAST(stg.ClientEWSWarrantySKU AS INT)) = 33 THEN 'CC'
        WHEN SUM(DISTINCT CAST(stg.ClientEWSWarrantySKU AS INT)) = 42 THEN 'DD'
    END AS EWSPlan
   ,stg.ProductSKU AS ProductType
   ,CAST(FORMAT(stg.EWSPurchaseDate, 'yyyy/MM/dd') AS VARCHAR) AS PlanStartDate
   ,'' AS PlanEndDate--,
FROM STAGE_ENROLLMENT AS stg (NOLOCK)
WHERE EXISTS (
    SELECT 1
    FROM #tmp t
    WHERE t.TransactionNo = stg.CustomerID AND t.StoreName = stg.StoreID
)

By applying these optimizations, we can significantly improve the performance of the query and reduce the table spool operation.

Last modified on 2023-09-08