SELECT * FROM Response WHERE BatchID=2 AND Response=1 AND Amount>=500Next query also finishes in about 2 seconds:
SELECT * FROM Response WHERE BatchID=2 AND Listcode NOT IN (SELECT CASE WHEN nListcode='' THEN oListcode ELSE nListcode END FROM RawData WHERE CampaignID=@CampaignID AND Mailed>0 GROUP BY CASE WHEN nListcode='' THEN oListcode ELSE nListcode END)However, following combination of the first two WHERE condition takes almost 3 minutes to complete.
SELECT * FROM Response WHERE BatchID=2 AND Response=1 AND Amount>=500 AND Listcode NOT IN (SELECT CASE WHEN nListcode='' THEN oListcode ELSE nListcode END FROM RawData WHERE CampaignID=@CampaignID AND Mailed>0 GROUP BY CASE WHEN nListcode='' THEN oListcode ELSE nListcode END)Can anyone tell me why and how to optimize this last query? TIA.