CODEWITHSUNDEEP
sqlsql-server-2008
user1025901
user1025901

Reputation: 1909

A better way to handle the below Sql query filtering condition

Consider the below SQL query

INSERT INTO [dbo].[PartnerCommissionData] 
SELECT X.*
FROM
(
        SELECT 
                cs.partner_id
                ,cs.quarter 
                ,cs.year
                ,cs.partner_currency_amount
                ,p.partner_email                        
                ,cs.report_status_id    
                ,Creation_dt = GETDATE()    
        FROM [dbo].[CommissionSummary] cs WITH(NOLOCK)
        INNER JOIN [dbo].[Partner] p WITH(NOLOCK) ON p.partner_id = cs.partner_id
        WHERE  
                cs.year = YEAR(@FirstDayOfQuarter)  
        AND     cs.quarter = @Quarter
        AND     cs.report_status_id IN (1,2)
    )X
WHERE 
    X.partner_id NOT IN (SELECT [Partner_Id] FROM [dbo].[PartnerCommissionData] WITH (NOLOCK))
AND X.quarter NOT IN (SELECT [Quarter] FROM [dbo].[PartnerCommissionData] WITH (NOLOCK))
AND X.year NOT IN (SELECT [Year] FROM [dbo].[PartnerCommissionData] WITH (NOLOCK))
AND X.report_status_id NOT IN (SELECT [CommissionStatus_id] FROM [dbo].[PartnerCommissionData] WITH (NOLOCK))

The filtering condition specified indicates that if the record already present in the PartnerCommissionData table, then it should not be inserted further. But as per me it's a kind of nasty implementation.

WHERE 
    X.partner_id NOT IN (SELECT [Partner_Id] FROM [dbo].[PartnerCommissionData] WITH (NOLOCK))
AND X.quarter NOT IN (SELECT [Quarter] FROM [dbo].[PartnerCommissionData] WITH (NOLOCK))
AND X.year NOT IN (SELECT [Year] FROM [dbo].[PartnerCommissionData] WITH (NOLOCK))
AND X.report_status_id NOT IN (SELECT [CommissionStatus_id] FROM [dbo].[PartnerCommissionData] WITH (NOLOCK))

How can we rewrite it in a better fashion?

Can we apply a merge or any other way....?

Thanks in advance

Upvotes: 0

Views: 69

Answers (3)

Turbot
Turbot

Reputation: 5227

Can you do something like this ? Don't need to left join (scanning another table). assume partner_id, quarter , year , report_status_id need to convert to nvarchar

   INSERT INTO [dbo].[PartnerCommissionData] 
    SELECT X.*
    FROM
    (
            SELECT 
                    cs.partner_id
                    ,cs.quarter 
                    ,cs.year
                    ,cs.partner_currency_amount
                    ,p.partner_email                        
                    ,cs.report_status_id    
                    ,Creation_dt = GETDATE()    
            FROM [dbo].[CommissionSummary] cs WITH(NOLOCK)
            INNER JOIN [dbo].[Partner] p WITH(NOLOCK) ON p.partner_id = cs.partner_id
            WHERE  
                    cs.year = YEAR(@FirstDayOfQuarter)  
            AND     cs.quarter = @Quarter
            AND     cs.report_status_id IN (1,2)
       )X
    WHERE 
    (x.partner_id + x.quarter + x.year + x.report_status_id) <> 
    (partner_id + x.quarter + x.year + x.report_status_id)

Upvotes: 1

Simon Wang
Simon Wang

Reputation: 2943

So if my understanding is correct, you are avoiding inserting data that might have duplicates, I changed your SQL to something as follow:

    INSERT INTO [dbo].[PartnerCommissionData] 
SELECT X.*
FROM
(
        SELECT 
                cs.partner_id
                ,cs.quarter 
                ,cs.year
                ,cs.partner_currency_amount
                ,p.partner_email                        
                ,cs.report_status_id    
                ,Creation_dt = GETDATE()    
        FROM [dbo].[CommissionSummary] cs WITH(NOLOCK)
        INNER JOIN [dbo].[Partner] p WITH(NOLOCK) ON p.partner_id = cs.partner_id
        WHERE  
                cs.year = YEAR(@FirstDayOfQuarter)  
        AND     cs.quarter = @Quarter
        AND     cs.report_status_id IN (1,2)
    )X
    LEFT JOIN [PartnerCommissionData] PCD WITH (NOLOCK)) 
            ON X.partner_id = PCD.[Partner_Id] OR X.quarter = PCD.[Quarter] 
                OR X.year = PCD.[Year] OR X.report_status_id = PCD.[CommissionStatus_id]
WHERE PCD.ID IS NULL

The thing I'm trying to do here is first LEFT JOIN the table and try to grab any data if they exist, and then using the IS NULL check to filter out any existing data, so that we do not need the NOT IN which doesn't perform well and not recommended.

Upvotes: 0

Matt Alcock
Matt Alcock

Reputation: 12891

Perform joins rather than inner selects. This is very messy SQL. Think in sets rather than procedures.

Upvotes: 0

Related Questions