| Author |
 Topic  |
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
 Posted - 04/03/2006 : 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: http://weblogs.sqlteam.com/mladenp
|
|
|
AndrewMurphy
Flowing Fount of Yak Knowledge
Ireland
2915 Posts |
Posted - 04/03/2006 : 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. |
 |
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 04/03/2006 : 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: http://weblogs.sqlteam.com/mladenp
|
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 04/03/2006 : 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 |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 04/03/2006 : 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: http://weblogs.sqlteam.com/mladenp
|
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 04/03/2006 : 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 |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 04/03/2006 : 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: http://weblogs.sqlteam.com/mladenp
|
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 04/03/2006 : 11:19:28
|
"about 5-10% better time"
How bizarre!
Kristen |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 04/03/2006 : 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: http://weblogs.sqlteam.com/mladenp
|
Edited by - spirit1 on 04/03/2006 11:55:35 |
|
|
|
rockmoose
SQL Natt Alfen
Sweden
3279 Posts |
Posted - 04/03/2006 : 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 |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
|
|
jen
Flowing Fount of Yak Knowledge
Sweden
4110 Posts |
Posted - 04/04/2006 : 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... |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 04/04/2006 : 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 |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 04/04/2006 : 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: http://weblogs.sqlteam.com/mladenp
|
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 04/04/2006 : 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 |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 04/04/2006 : 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: http://weblogs.sqlteam.com/mladenp
|
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 04/04/2006 : 16:16:24
|
No as you don't know what's in the cache each time a query runs.
Tara Kizer
aka tduggan |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 04/04/2006 : 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: http://weblogs.sqlteam.com/mladenp
|
Edited by - spirit1 on 04/04/2006 16:19:23 |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 04/04/2006 : 16:20:14
|
Please see the two DBCC statements in BOL for what they do.
Tara Kizer
aka tduggan |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 04/04/2006 : 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: http://weblogs.sqlteam.com/mladenp
|
Edited by - spirit1 on 04/04/2006 16:26:43 |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 04/04/2006 : 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 |
|
|
|
Topic |
|
indexes...
Forum Locked
