QuickDrawMcgraw QuickDrawMcgraw - 1 year ago 43
SQL Question

UNION clause performance issue with sql

I have the below union clause causing the overall stored procedure to run very slowly. I am using union to collect information from the same table, but the second and third union clauses join to other tables too for filtering. I understand this may not be the best coding approach and was hoping someone could guide me on a better syntax.

select ss.int_tran_id
from status ss
where ss.stage in ('ACHPayment_Confirmed', 'HIFV4', 'HIFV5_FTRINF', 'Payment_HIFV5_FTRINF')
select ss.int_tran_id
from status ss, references rf
where ss.int_tran_id = rf.int_tran_id
and ss.stage = 'PREVDAY'
and rf.mid_ref IS NOT NULL
select ss.int_tran_id
from status ss, app_data ad, ach aa
where ss.int_tran_id = ad.int_tran_id
and ad.app_data_id = aa.ach_id
and ss.stage = 'PREVDAY'
and aa.par_number IS NOT NULL

Answer Source

I think you could rewrite this as a single query on the status table, using ORs to test each scenario, like so:

SELECT ss.int_tran_id
FROM   status SS
WHERE  ss.stage in ('ACHPayment_Confirmed', 'HIFV4', 'HIFV5_FTRINF', 'Payment_HIFV5_FTRINF')
OR     (ss.stage = 'PREVDAY'
                     FROM   references rf
                     WHERE  ss.int_tran_id = rf.int_tran_id
                     AND    rf.mid_ref IS NOT NULL)
                        FROM   app_data ad
                               INNER JOIN ach aa
                                 ON (ad.app_data_id = aa.ach_id)
                        WHERE  ss.int_tran_id = ad.int_tran_id
                        AND    aa.par_number IS NOT NULL)));