How to improve query performance when using NOT IN

Question

I am joining two tables many to many relationship.

In my final result I have multiple ClassCode for each PolicyNumber. Looks like that:

Now I need to exclude the whole PolicyNumber with PaidLosses if @ClassCode parameter in SSRS been choosen. So, when I use NOT IN to eliminate PolicyNumber it spinning forever.

select      
            cte1.PolicyNumber,
            cte1.TransactionEffectiveDate,
            cc.ClassCode,
            CASE
                WHEN ROW_NUMBER() OVER (PARTITION BY cte1.QuoteID, cte1.PolicyNumber, cc.TransactionEffectiveDate ORDER BY (SELECT 0))=1 THEN cte1.WP 
                ELSE 0
            END  as WP--,
from        cte1 inner join tblClassCodesPlazaCommercial cc on cte1.PolicyNumber=cc.PolicyNumber AND cte1.QuoteID=cc.QuoteID AND cte1.TransactionEffectiveDate=cc.TransactionEffectiveDate
            AND cc.PolicyNumber IN (SELECT PolicyNumber FROM tblClassCodesPlazaCommercial WHERE ClassCode NOT IN (@ClassCode))

Is any other way to improve query performance in my case?

The whole query is below:

DECLARE @ClassCode int = 5151
;with cte1
as
(
SELECT      QuoteID,
            CONVERT(VARCHAR(10),TransactionEffectiveDate,101) as TransactionEffectiveDate,
            PolicyNumber,
            SUM(WrittenPremium) as WP                           
FROM        PlazaInsuranceWPDataSet    

WHERE        State IN ('CA','NV','AZ') 
GROUP BY    
            PolicyNumber,
            QuoteID,
            TransactionEffectiveDate    
),
cte3
as
(
select      
            cte1.PolicyNumber,
            cte1.TransactionEffectiveDate,
            cc.ClassCode,
            CASE
                WHEN ROW_NUMBER() OVER (PARTITION BY cte1.QuoteID, cte1.PolicyNumber, cc.TransactionEffectiveDate ORDER BY (SELECT 0))=1 THEN cte1.WP 
                ELSE 0
            END  as WP--,
from        cte1 inner join tblClassCodesPlazaCommercial cc on cte1.PolicyNumber=cc.PolicyNumber AND cte1.QuoteID=cc.QuoteID AND cte1.TransactionEffectiveDate=cc.TransactionEffectiveDate
            AND cc.PolicyNumber IN (SELECT PolicyNumber FROM tblClassCodesPlazaCommercial WHERE ClassCode NOT IN (@ClassCode))
)
select  
        c.YearNum,
        c.MonthNum, 
        SUM(WP) as WP         
from    cte3 RIGHT JOIN tblCalendar  c ON c.YearNum=YEAR(TransactionEffectiveDate) AND c.MonthNum=MONTH(TransactionEffectiveDate)
WHERE   c.YearNum <>2017
GROUP BY    
            c.YearNum,
            c.MonthNum
ORDER BY    c.YearNum desc,
            c.MonthNum

Sorry, but you're using `NOT IN` instead of `<>` to test a single integer value:`WHERE ClassCode NOT IN (@ClassCode)`? Have you looked into `EXISTS` for your other `IN` correlated subqueries? Or an `OUTER JOIN`? — HABO, Nov 16 '16 at 21:09
What does the execution plan look like? Is there an index on the column you are doing the "NOT IN" on? — James Casey, Nov 17 '16 at 11:39

score 1 · Accepted Answer · answered Nov 17 '16 at 16:21

For CTE3, I think you can write this way, and it might help improve the performance

cte3
as
(
select      
            cte1.PolicyNumber,
            cte1.TransactionEffectiveDate,
            cc.ClassCode,
            CASE
                WHEN ROW_NUMBER() OVER (PARTITION BY cte1.QuoteID, cte1.PolicyNumber, cc.TransactionEffectiveDate ORDER BY (SELECT 0))=1 THEN cte1.WP 
                ELSE 0
            END  as WP--,
from        cte1 
inner join tblClassCodesPlazaCommercial cc 
on cte1.PolicyNumber=cc.PolicyNumber 
    AND cte1.QuoteID=cc.QuoteID 
    AND cte1.TransactionEffectiveDate=cc.TransactionEffectiveDate

where cc.ClassCode NOT IN (@ClassCode))
    )

So, the goal was to eliminate not just ClassCode, but also the whole PolicyNumber if @ClassCode selected. I was fighting it for 3 days already, using `EXISTS`, `NOT IN`, `NOT EXISTS` and even `LEFT ANTI SEMI JOIN`. The performance was taking forever. And you didn't just made my day - you made my whole week!!!!! I cant believe this simple `WHERE` clause made everything works and made it so fast. THANK YOU SOOOOO MUCH!!!! — Serdia, Nov 17 '16 at 16:47

How to improve query performance when using NOT IN

1 Answers1