Why is the following query so bad performance-wise

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?

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

SELECT
  @TempCardNumber = CardNumber 
FROM
    (
    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?