CODEWITHSUNDEEP
sql-server-2005t-sqlperformanceexists
Mobs
Mobs

Reputation: 1562

TSQL NOT EXISTS Why is this query so slow?

Debugging an app which queries SQL Server 05, can't change the query but need to optimise things.

Running all the selects seperately are quick <1sec, eg: select * from acscard, select id from employee... When joined together it takes 50 seconds.

Is it better to set uninteresting accesscardid fields to null or to '' when using EXISTS?

  SELECT * FROM ACSCard
    WHERE NOT EXISTS
     ( SELECT Id FROM Employee 
              WHERE Employee.AccessCardId = ACSCard.acs_card_number )
    AND NOT EXISTS
     ( SELECT Id FROM Visit 
               WHERE Visit.AccessCardId = ACSCard.acs_card_number ) 
  ORDER by acs_card_id

Upvotes: 2

Views: 3150

Answers (2)

Ted Elliott
Ted Elliott

Reputation: 3493

Do you have indexes on Employee.AccessCardId, Visit.AccessCardId, and ACSCard.acs_card_number?

Upvotes: 3

OMG Ponies
OMG Ponies

Reputation: 332571

The SELECT clause is not evaluated in an EXISTS clause. This:

WHERE EXISTS(SELECT 1/0
               FROM EMPLOYEE)

...should raise an error for dividing by zero, but it won't. But you need to put something in the SELECT clause for it to be a valid query - it doesn't matter if it's NULL or a zero length string.

In SQL Server, NOT EXISTS (and NOT IN) are better than the LEFT JOIN/IS NULL approach if the columns being compared are not nullable (the values on either side can not be NULL). The columns compared should be indexed, if they aren't already.

Upvotes: 2

Related Questions