Why Index Scan instead of Index Seek

Question

Why Index Scan instead of Index Seek

Steve-443559

SSCrazy

Points: 2309
More actions
July 11, 2011 at 7:43 am

#243874

I have a query
select top (1) A, B, C
from
TABLE1 fd (nolock)
WHERE
(
fd.B< '2011-06-01 17:17:10.323'
)
or
(
fd.B= '2011-06-01 17:17:10.323' and
fd.C> 6029040
)
order by
fd.B desc, fd.C asc
There is an index on the table (which has millions of rows)
CREATE NONCLUSTERED INDEX Index1 ON Table1
(
B DESC,
C ASC,
A ASC
)
Why would the query be doing a SCAN vs a SEEK? I've rebuild and updated statistics on it. If I run the statement using only one of the where clauses it chooses the seek, but not when both WHERE filters are in.
I'm using SQL 2008 Enterprise. Please let me know if you need more info.
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

It's pretty usual to end up with scans when you have OR statements in the Where clause.

There can be other reasons for them, including the selectivity of the leading edge of the index, the number of rows being fetched, selecting ranges of rows instead of individual rows, avoidance of bookmark lookups, and others.

Is the query performing poorly?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Lowell SSC Guru Points: 323518 More actions · Answer 2

I'm pretty sure it's the OR.

with a single value, it can do a SEEK, but with the OR, it has to scan to check the values that don't match the first condition.

you could union the two queries together , or join them, so they can independantly do SEEKS:

select top (1) A, B, C

FROM

(

select top (1)

A, B, C

from

TABLE1 fd (nolock)

WHERE fd.B < '2011-06-01 17:17:10.323'

order by

fd.B desc, fd.C asc

UNION

select top

(1) A, B, C

from

TABLE1 fd (nolock)

WHERE fd.B= '2011-06-01 17:17:10.323'

and fd.C> 6029040

order by

fd.B desc, fd.C asc

) MyAlias

order by

B desc, C asc

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Explained here: http://sqlinthewild.co.za/index.php/2011/05/03/indexing-for-ors/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

kramaswamy SSCoach Points: 18181 More actions · Answer 4

I'm curious, why is it that using UNION instead of OR makes for performance increases? For most situations like this, the query optimiser is usually smart enough to do the work itself, and "tricking" it usually makes the performance worse instead of better.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Edit: Nevermind

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323518 More actions · Answer 6

kramaswamy (7/11/2011)
I'm curious, why is it that using UNION instead of OR makes for performance increases? For most situations like this, the query optimiser is usually smart enough to do the work itself, and "tricking" it usually makes the performance worse instead of better.

Gails article explains part of it very nicely. thank you Gail. I like the info about how an OR is going to expand the dataset and not reduce it;

the technique above doesn't trick anything, it gets the data in a way that might take advantage of indexes and produce a seek. just another tool in the toolbox.

it's just doing a classic divide and conquer technique;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Though do note that OR and UNION may produce different results.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass