Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 indexes...

Author  Topic 

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-04-03 : 09:19:01
why does

select * from orders

make a clustered index scan

and

select * from orders
where orderid > 200

make a clustered index seek?

shoudn't the first one also be a seek
by everything that's logical?


Go with the flow & have fun! Else fight the flow
Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-04-03 : 09:49:00
no WHERE condition....SCAN for all records....logical
WHERE condition....and useful information provided in the WHERE clause....seek, cause you are filtering out at least some records

Also the SELECT * bit would influence the tendancy to go with a scan.
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-04-03 : 10:03:47
yeah.. it's same if i enum the columns...

you see for me logicaly if you have a clustered index and you can do a seek why do a scan?
even if it's all data...

the above returns same data but seek is of course faster....

Go with the flow & have fun! Else fight the flow
Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-04-03 : 10:07:51
"you see for me logicaly if you have a clustered index and you can do a seek why do a scan?
even if it's all data
"

Well, here's my $0.02 worth:

Because its all the data ... no sense reading an index, and re-directing via it to the data, if you are going to read every record.

But ">200" (if the first column of a clustered index) is always going to be a "range job", so ideal candidate for index seek.

Might still be a good candidate for a non-clustered index - but probably only if the estimator guesstimates less than 50% of the index will be hit, or some-such proportion.

Kristen
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-04-03 : 10:11:27
i understand all that
it's just funny if you ask me

if you have an identity PK >0
you could do
select * from table where id > -1

and have better perfomance than simple
select * from table where id > -1

and you'd know you get back the same data....


Go with the flow & have fun! Else fight the flow
Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-04-03 : 10:13:08
"select * from table where id > -1"

Does that do an Index Seek? If the optimiser can figure out that its 100% of the table then a Scan would be better me-thinks!

Kristen
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-04-03 : 10:18:08
try it on northwind...
i get a seek for > -1
and about 5-10% better time.

Go with the flow & have fun! Else fight the flow
Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-04-03 : 11:19:28
"about 5-10% better time"

How bizarre!

Kristen
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-04-03 : 11:55:03
run this code each batch separatly. each batch 10 times...
i constantly get 2.8-ish secs for 1st and 2.5-ish for second.

set nocount on
declare @i int , @time datetime
set @i = 0
set @time = getdate()
while @i<50
begin
select * from orders
set @i = @i+1
end
print('iters scan:' + convert(varchar(20), getdate() - @time, 114))
set nocount off

go

set nocount on
declare @i int , @time datetime
set @i = 0
set @time = getdate()
while @i<50
begin
select * from orders
where orderid > -1
set @i = @i+1
end
print('iters seek:' + convert(varchar(20), getdate() - @time, 114))
set nocount off


Go with the flow & have fun! Else fight the flow
Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-04-03 : 15:53:27
Same results as Mladen.

iters scan:00:00:02:017
iters seek:00:00:01:860

Seems the same on SQL2005 as well.

Bizarre.

rockmoose
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-04-04 : 05:30:20
this must be blogged!

Go with the flow & have fun! Else fight the flow
Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-04 : 11:07:19
I encountered this before...

if you add a criteria for the search that will still provide the data you need, if forces an index seek

actually took advantage on this one to optimize those nasty reports the dev team back then

they asked me about it and i said, "oh it's just to make your queries faster"
they say "but why put a where clause when not needed"
me say "are you getting the same data?"
they say "yes"
me say "so the reports are still accurate but generated faster?"
they say "uhm yes but..."
me say "so you're complaining that this just trimmed down your query time to half???"



--------------------
keeping it simple...
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-04 : 13:37:49
Here's my question posted on Mladen's blog:

Don't you need to:

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

in between each in order for your results to be accurate? You've got to empty the cache in order to have a true test.

------


I'm not saying that this would change the results, but those two commands are necessary when doing performance analysis.


Tara Kizer
aka tduggan
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-04-04 : 14:21:51
well... that's true ... but i disagree

my reasoning being:
1. you have a query
2. you run it constantly so it gets cached and thus runs faster.
3. that's why i didn't run those 2.

my reasoning could be wrong of course


Go with the flow & have fun! Else fight the flow
Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-04 : 14:25:49
You can't compare results unless you start from the same baseline. Those two statements ensure you have the same baseline.

Tara Kizer
aka tduggan
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-04-04 : 16:13:22
emm... maybe i'm being thick here
but if both statements are cached... doesn't that give them the same baseline??

Go with the flow & have fun! Else fight the flow
Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-04 : 16:16:24
No as you don't know what's in the cache each time a query runs.

Tara Kizer
aka tduggan
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-04-04 : 16:18:46
you mean you don't know which statement is cached?
i thought both of them are?


Go with the flow & have fun! Else fight the flow
Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-04 : 16:20:14
Please see the two DBCC statements in BOL for what they do.

Tara Kizer
aka tduggan
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-04-04 : 16:26:08
i meant if you don't run the DBCC statements.
the sql selects get cached the first time they're run. so next n times they get reused from cache.
doesn't that give you the same baseline?
if none of the 2 dbcc's are ran.

what am i missing here?

Go with the flow & have fun! Else fight the flow
Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-04 : 16:32:36
No it doesn't give you the same baseline. When doing performance analysis, you must run those two statements prior to each in order to have accurate results. I believe Adam Machanic has information on his blog about it. I've known about it for a while, but I believe I've seen a blog recently about it.

I'm not saying that it will change the results in this instance, but it would in other instances.

Tara Kizer
aka tduggan
Go to Top of Page
    Next Page

- Advertisement -