CODEWITHSUNDEEP
sqlsql-server
C. Brendan Law
C. Brendan Law

Reputation: 41

Query Left Join with OR Inefficiency

I have a table (200K rows) with a field called "Campaign". I have a separate table list of campaigns with additional information. I want to join on where (campaign_id = campaign) OR (cid.spend_source = a.Traffic_Source AND a.Campaign = cid.Campaign_Name).

The problem I'm facing is that the OR statement is killing efficiency, causing a nested loop with 400 million rows.

What's a better method?

UPDATE a
SET    a.campaign_name = cid.Campaign_Name,
       a.Campaign_ID = cid.Campaign_ID
FROM   database.dbo.table a
       LEFT JOIN carb.dbo.carb_lookup_campaignid cid
              ON cid.Campaign_ID = a.Campaign
                  OR ( cid.spend_source = a.Traffic_Source
                       AND a.Campaign = cid.Campaign_Name )

Upvotes: 0

Views: 70

Answers (3)

Jim Macaulay
Jim Macaulay

Reputation: 5141

Always use MERGE statement while updating from another table. You can use below query:

MERGE INTO a
USING 
(SELECT * FROM a
 LEFT OUTER JOIN carb_lookup_campaignid cid
ON (ON cid.Campaign_ID = a.Campaign)
OR ( cid.spend_source = a.Traffic_Source
                       AND a.Campaign = cid.Campaign_Name )
)
WHEN MATCHED
THEN
UPDATE 
SET
a.campaign_name = cid.Campaign_Name,
a.Campaign_ID = cid.Campaign_ID

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

Use two separated updated

  UPDATE a
  SET    a.campaign_name = cid.Campaign_Name,
         a.Campaign_ID = cid.Campaign_ID
  FROM   database.dbo.table a
  LEFT JOIN carb.dbo.carb_lookup_campaignid cid              
          ON cid.Campaign_ID = a.Campaign
  ;

and 

  UPDATE a
  SET    a.campaign_name = cid.Campaign_Name,
         a.Campaign_ID = cid.Campaign_ID
  FROM   database.dbo.table a
  LEFT JOIN carb.dbo.carb_lookup_campaignid cid               
              ON ( cid.spend_source = a.Traffic_Source
                   AND a.Campaign = cid.Campaign_Name ) 
  ;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Do two separate left joins:

UPDATE a
    SET a.campaign_name = coalesce(cidss.Campaign_Name, cidlc.Campaign_Name),
        a.Campaign_ID = coalesce(cidss.Campaign_ID, cidlc.Campaign_ID)
    FROM database.dbo.table a LEFT JOIN
         carb.dbo.carb_lookup_campaignid cidss
         ON cidss.Campaign_ID = a.Campaign and
            cidss.spend_source = a.Traffic_Source LEFT JOIN
         carb.dbo.carb_lookup_campaignid cidlc
         ON cidlc.Campaign_ID = a.Campaign and
            cidlc.Campaign_Name = a.Campaign and
            cidss.Campaign_ID is null
     WHERE cidss.Campaign_ID is not null or cidls.Campaign_ID is not null;

Each individual LEFT JOIN can take advantage of the appropriate index (Campaign_ID, spend_source) and (Campaign_ID, Campaign_Name).

Upvotes: 1

Related Questions