1

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'))
Chipmunk_da
  • 467
  • 2
  • 9
  • 27
  • 2
    Rewrite it as a `UNION ALL` – HoneyBadger Nov 27 '20 at 11:55
  • 1
    How long takes inserting 43m rows (without join) into temp table? If this is quick, then you likely have no good (covering) index on lookup table, also you could try using `merge` query hint. If already inserting 43m rows without join takes forever, then you need better hardware. – Arvo Nov 27 '20 at 11:55
  • @HoneyBadger can you please provide a hint on how this can be transformed into a `UNION ALL` query? – Chipmunk_da Nov 27 '20 at 12:03
  • Can `txn.Txn_Type` be anything other than Sell or Buy? If not, select first all Sellers and union them with the Buyers. If it can be something else you need to union those others again – HoneyBadger Nov 27 '20 at 12:07
  • @Arvo I just inserted into temp table without any joins and it took around 3 minutes - so fairly quick. Given the fields in the lookup table, do you think I should create an index on `lkp.ID`? Also, how to rewrite this using `MERGE` query hint? – Chipmunk_da Nov 27 '20 at 12:07
  • How many rows from `master_lookup ` are of `Txn_Type = 'Sell' or 'Byu'`? – gotqn Nov 27 '20 at 12:13
  • @gotqn All of them – Chipmunk_da Nov 27 '20 at 12:17
  • 1
    About covering index - you could create index on lookup table fields (id, name, address). If your lookup table has clustered index on id and does not contain many other fields, then using query hints may be better idea. HoneyBadger's union idea should help either. – Arvo Nov 27 '20 at 12:19
  • 1
    One more idea (you have only two possible transaction types) - you could link your lookup table twice and return your lookup table fields using isnull() or coalesce(). All this usually need experimenting, looking at query plans etc. – Arvo Nov 27 '20 at 12:21

1 Answers1

2

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
HoneyBadger
  • 14,750
  • 3
  • 34
  • 48
  • `txn.Txn_Type` can only be Sell or Buy. I'm running the query now with your suggestion..will post an update as soon as it finishes. Fingers crossed! – Chipmunk_da Nov 27 '20 at 12:19
  • If you only have 'Buy' and 'Sell' you won't need the last UNION (that is also likely the least performing) – HoneyBadger Nov 27 '20 at 12:20
  • Yes, have exluded the last `UNION` – Chipmunk_da Nov 27 '20 at 12:21
  • It just finished running and took around 5 minutes!! Great suggestion, thanks for your help! – Chipmunk_da Nov 27 '20 at 12:24
  • Does this change depending on whether the columns are the same in the OR? i.e. is there a difference between "Id = 4 OR Id = 6" and perhaps "Id = 4 OR NonIndexedName = 'Jon'". I was under the impression using an OR killed the index use entirely but this answer https://stackoverflow.com/a/13866221/4662037 seems to suggest you can still use an OR as long as you are looking at the same column. – Murphybro2 Oct 19 '22 at 14:58