After asking this question, I got to thinking about why a certain query was doing all the problems.

In short, there was a query which took 500ms, I ran some execution plans and applied the recommended indexes but it only cut off about 50-60ms.

After running it again I found out that the following query(which occur in multiple locations) was really slow:

SELECT @TempCardNumber = CardNumber 
FROM Cards 
WHERE (CardNumber=@CardNumber or FullCardNumber=@CardNumber)

When I changed it to be

SELECT @TempCardNumber = CardNumber 
FROM Cards 
WHERE (CardNumber=@CardNumber)

It ran super fast, and even the removal of indexes didn't matter too much, leading me to believe this was the main bottleneck, but I don't understand.. whats so wrong about it?

  • Do you have an index on FullCardNumber ? May 17, 2012 at 8:28
  • And since you probably need only one value, you could modify it to SELECT TOP(1) ... May 17, 2012 at 8:30
  • @ypercube: then you need ORDER BY...
    – gbn
    May 17, 2012 at 8:36

1 Answer 1


OR is not usually "SARGable" when you use different columns

That is, case you can optimise for either CardNumber or FullCardNumber searches with an index on each column but not both columns. This is a "seek".

An index on both columns won't work because you it is OR: values for every row must be examined. This is a scan.

If the condition was AND, SQL Server could do a residual lookup on FullCardNumber after findng CardNumber. Seek followed by a secondary seek basically.

Anyway, try this to remove the OR scan and have 2 individual efficient seeks

  @TempCardNumber = CardNumber 
    SELECT CardNumber 
    FROM Cards 
    WHERE CardNumber = @CardNumber
    UNION -- removed dupes
    SELECT CardNumber 
    FROM Cards 
    WHERE FullCardNumber = @CardNumber
    ) X

Note that the query assume you only get one row: is this intended?

  • I guess this query intends to act as exists, after there is a check if TempCardNumber got the value of CardNumber...
    – Mithir
    May 17, 2012 at 8:56
  • Also, did you mean to have the @TempCardNumber in the inner query? it looks like it should just be 'select CardNumber From Cards Where CardNumber=@CardNumber UNION..."
    – Mithir
    May 17, 2012 at 9:13
  • @Mithir: oops, copy/paste error
    – gbn
    May 17, 2012 at 9:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.