SQLTeam.com | Weblogs | Forums

Sub Queries left outer join is slow and take a long time to run

I have this query and it take about 4.5 minutes to return 253 records. Can someone tell me how to make left join in sub query more efficient way to reduce the time to run this query

SELECT A.EMPLOYEE, B.CHECK_DATE, B.DED_CODE, B.DED_AMT
FROM
(SELECT EMPLOYEE FROM BENEFIT WHERE PLAN_CODE = 'FSAM' AND (STOP_DATE='1/1/1753' OR STOP_DATE > GETDATE())
AND YEAR(START_DATE) = 2020) A LEFT OUTER JOIN
(SELECT EMPLOYEE, DED_AMT, DED_CODE, CHECK_DATE FROM PAYDEDUCTN WHERE PAYDEDUCTN.DED_CODE = 'FSME' AND (CHECK_DATE = '4/10/2020')) B
ON A.EMPLOYEE=B.EMPLOYEE
ORDER BY A.EMPLOYEE

The snippet of code you have above makes a non-SARGable criteria that defeats index seeks and causes index scans. It should be rewritten as follows to make it SARGable

WHERE START_DATE >= '2020-01-01' AND START_DATE < '2021-01-01'

Of course, that may not be the only problem but I don't have your data to test with nor do I use MySQL.