How to improve query performance for expensive table spool

Question

There is a requirement to get the data from a single stage table with the below query. However, the query is taking lots of time to execute. How to optimize the below query? Execution plan says there is table spool which is 50% of entire cost and estimated operated cost in 12.22.

CREATE TABLE #Enrollment_grp 
(
rowcounts int,
CustomerID NVARCHAR(100),
EWSPurchaseDate  DATETIME,
EWSPlanType nvarchar(200),
ContractStatus nvarchar(200),
TerminationDate  DATETIME,
StoreId [nvarchar](100) NULL,
CreatedDate DATETIME NULL,
ProductSKU [nvarchar](200) NULL,
ClientEWSWarrantySKU [nvarchar](100) NULL,
COUNTSL int null
)

CREATE CLUSTERED INDEX IDX_Enrollment_GRP_CustomerID ON #Enrollment_grp(CustomerID)

CREATE NONCLUSTERED INDEX IDX_Enrollment_GRP_EWSPUR ON #Enrollment_grp(EWSPurchaseDate)

CREATE NONCLUSTERED INDEX IDX_Enrollment_GRP_TERMDATE ON #Enrollment_grp(TerminationDate)

insert INTO #Enrollment_grp(CustomerID,EWSPurchaseDate,rowcounts,EWSPlanType,ContractStatus,TerminationDate,StoreID,CreatedDate,ProductSKU,ClientEWSWarrantySKU,COUNTSL)
(
SELECT CustomerID,EWSPurchaseDate,rowcounts,EWSPlanType,ContractStatus,temp.TerminationDate,temp.StoreID,temp.CreatedDate,temp.ProductSKU,temp.ClientEWSWarrantySKU,COUNTSL
from
(

SELECT x.CustomerID,x.EWSPurchaseDate,ROW_NUMBER() over(PARTITION BY x.CustomerID,x.EWSPlanType ORDER BY x.CreatedDate DESC) as rowcounts,
count(EWSPlanType) over (PARTITION BY x.CustomerID,x.EWSPlanType ORDER BY x.CreatedDate) COUNTSL,x.EWSPlanType,x.ContractStatus
,x.TerminationDate,X.StoreID,X.CreatedDate,x.ProductSKU,x.ClientEWSWarrantySKU
FROM STAGE_ENROLLMENT AS x (NOLOCK) WHERE x.ClientID = 1
) AS temp WHERE temp.rowcounts=1 AND cast(temp.TerminationDate AS DATE)>='2018-10-01'
)

CREATE TABLE #tmp
(
    ID int IDENTITY(1,1) PRIMARY KEY,
    TransactionNo [nvarchar](100) NULL,
    StoreName [nvarchar](100) NULL,
    EWSPlan NVARCHAR(200),
    EventDate DATETIME NULL,
    ProductType [nvarchar](200) NULL,
    PlanStartDate [varchar](50) NULL,
    PlanEndDate [varchar](50) NULL
)

CREATE NONCLUSTERED INDEX IX_tmp_TN
ON #tmp(TransactionNo)


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<='2018-10-31' AND stg.TerminationDate>='2018-10-01'
AND stg.ContractStatus = 1
AND stg.CustomerID IN (SELECT CustomerID FROM #Enrollment_grp where EWSPurchaseDate<='2018-10-31' AND TerminationDate>='2018-10-01' GROUP BY CustomerID HAVING COUNT(DISTINCT EWSPlanType) = 3 )
AND stg.ProductSKU IN (SELECT ProductSKU FROM #Enrollment_grp where EWSPurchaseDate<='2018-10-31' AND TerminationDate>='2018-10-01' GROUP BY ProductSKU HAVING COUNT(DISTINCT EWSPlanType) = 3 )
AND stg.StoreID IN (SELECT StoreID FROM #Enrollment_grp where EWSPurchaseDate<='2018-10-31' AND TerminationDate>='2018-10-01' GROUP BY StoreID HAVING COUNT(DISTINCT EWSPlanType) = 3 )
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 
)

Can anybody provide any suggestion for the above scenario?

Answer 1

I can suggest to you using EXISTS instead of IN. Could you apply these following changes and check whether faster or nor :

Before you insert into #tmp table apply this script :

SELECT CustomerID,ProductSKU,StoreID INTO #EnrTemp FROM #Enrollment_grp WHERE EWSPurchaseDate <= '2018-10-31' AND TerminationDate >= '2018-10-01' GROUP BY CustomerID,ProductSKU,StoreID HAVING COUNT(DISTINCT EWSPlanType)= 3

After that remove these where conditions :

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

and put the following condition :

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

It should looks like :

SELECT CustomerID,ProductSKU,StoreID INTO #EnrTemp FROM #Enrollment_grp WHERE EWSPurchaseDate <= '2018-10-31' AND TerminationDate >= '2018-10-01' GROUP BY CustomerID,ProductSKU,StoreID HAVING COUNT(DISTINCT EWSPlanType)= 3

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 <= '2018-10-31'
    AND stg.TerminationDate >= '2018-10-01'
    AND stg.ContractStatus = 1
    AND EXISTS (SELECT 1 FROM #EnrTemp E WHERE STG.CustomerID = E.CustomerID AND STG.ProductSKU = E.ProductSKU AND STG.StoreID = E.StoreID  )
    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
    )