Slow query when used as EXISTS subquery

Question

I have the following query:

SELECT I.InsuranceID
FROM Insurance I
INNER JOIN JobDetail JD ON I.AccountID = JD.AccountID
WHERE I.InsuranceLookupID IS NULL
AND JD.JobID = 28

It executes in about a second. When used as a subquery as follows:

IF EXISTS(
SELECT I.InsuranceID
FROM Insurance I
INNER JOIN JobDetail JD ON I.AccountID = JD.AccountID
WHERE I.InsuranceLookupID IS NULL
AND JD.JobID = 28
)
SELECT 1
ELSE
SELECT 0

It takes 90 seconds. It's my understanding that EXISTS is supposed to be optimized to stop after finding the first record. Why would this take longer?

Please see http://stackoverflow.com/questions/424212/performance-of-sql-exists-usage-variants for good information on EXISTS. — StAlphonzo, Oct 04 '11 at 17:36

score 5 · Accepted Answer · edited May 23 '17 at 11:59

I've seen this myself.

I can guess that EXISTS is better in a WHERE clause because it gives a semi-join which is set based, And exactly what you need.

In an IF, this isn't clear to the optimiser. That is, there is nothing to semi-join too. This should hopefully be the same (bad that is):

SELECT 1 WHERE EXISTS (SELECT I.InsuranceID
    FROM Insurance I
    INNER JOIN JobDetail JD ON I.AccountID = JD.AccountID
    WHERE I.InsuranceLookupID IS NULL
    AND JD.JobID = 28)

You could to this though

SELECT SIGN(COUNT(*))
FROM Insurance I
INNER JOIN JobDetail JD ON I.AccountID = JD.AccountID
WHERE I.InsuranceLookupID IS NULL
AND JD.JobID = 28

It is optimised in some circumstances:
What's the best to check if item exist or not: Select Count(ID)OR Exist(...)?

Not sure what confuses the optimiser...

Slow query when used as EXISTS subquery

1 Answers1