Why is the following query so bad performance-wise
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?