Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Do you have an index on FullCardNumber ? –  ypercube May 17 '12 at 8:28
    
And since you probably need only one value, you could modify it to SELECT TOP(1) ... –  ypercube May 17 '12 at 8:30
    
@ypercube: then you need ORDER BY... –  gbn May 17 '12 at 8:36
add comment

1 Answer

up vote 9 down vote accepted

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?

share|improve this answer
    
I guess this query intends to act as exists, after there is a check if TempCardNumber got the value of CardNumber... –  Mithir May 17 '12 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 '12 at 9:13
    
@Mithir: oops, copy/paste error –  gbn May 17 '12 at 9:14
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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