CODEWITHSUNDEEP
sql-server
Chipmunk_da
Chipmunk_da

Reputation: 507

Extremely slow SQL query due to LEFT JOIN with OR condition

I have the following SQL query with a LEFT JOIN with an OR condition which makes it almost unworkable - took over 12 hours and then had to be cancelled as it finished to complete.

Is there a better way to write it so it's runnable?

DROP TABLE IF EXISTS #temp_t
SELECT 
    txn.*
    ,lkp.ID, lkp.NAME, lkp.ADDRESS
INTO #temp_t
FROM    master_txn as txn --43m rows
LEFT JOIN   master_lookup as lkp --280k rows
    ON ((txn.lkp_ID_1 = lkp.ID AND txn.Txn_Type = 'Sell')
    OR (txn.lkp_ID_2 = lkp.ID AND txn.Txn_Type = 'Buy'))

Upvotes: 1

Views: 2125

Answers (1)

HoneyBadger
HoneyBadger

Reputation: 15150

A UNION usually works much quicker than OR's in a join. Something like this:

DROP TABLE IF EXISTS #temp_t
SELECT  txn.* -- It's much better to mention the specific columns
,       lkp.ID
,       lkp.NAME
,       lkp.ADDRESS
INTO    #temp_t
FROM    master_txn as txn 
LEFT JOIN   master_lookup as lkp 
    ON      txn.lkp_ID_1 = lkp.ID 
WHERE   txn.Txn_Type = 'Sell'

UNION ALL

SELECT  txn.* 
,       lkp.ID
,       lkp.NAME
,       lkp.ADDRESS
FROM    master_txn as txn 
LEFT JOIN   master_lookup as lkp 
    ON      txn.lkp_ID_2 = lkp.ID 
WHERE   txn.Txn_Type = 'Buy'

UNION ALL
-- If Txn_Type can be anything else than Buy or Sell, including NULL
SELECT  txn.* 
,       NULL
,       NULL
,       NULL
FROM    master_txn as txn 
WHERE   txn.Txn_Type NOT IN ('Buy', 'Sell')
    OR  txn.Txn_Type IS NULL

Upvotes: 2

Related Questions