SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 indexes...
 Forum Locked
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 04/03/2006 :  09:19:01  Show Profile  Visit spirit1's Homepage
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
2916 Posts

Posted - 04/03/2006 :  09:49:00  Show Profile
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

Slovenia
11751 Posts

Posted - 04/03/2006 :  10:03:47  Show Profile  Visit spirit1's Homepage
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
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 04/03/2006 :  10:07:51  Show Profile
"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

Slovenia
11751 Posts

Posted - 04/03/2006 :  10:11:27  Show Profile  Visit spirit1's Homepage
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
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 04/03/2006 :  10:13:08  Show Profile
"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

Slovenia
11751 Posts

Posted - 04/03/2006 :  10:18:08  Show Profile  Visit spirit1's Homepage
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
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

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

How bizarre!

Kristen
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 04/03/2006 :  11:55:03  Show Profile  Visit spirit1's Homepage
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
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 04/03/2006 :  15:53:27  Show Profile
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

Slovenia
11751 Posts

Posted - 04/04/2006 :  05:30:20  Show Profile  Visit spirit1's Homepage
this must be blogged!

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

jen
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 04/04/2006 :  11:07:19  Show Profile  Send jen a Yahoo! Message
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

USA
37129 Posts

Posted - 04/04/2006 :  13:37:49  Show Profile  Visit tkizer's Homepage
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

Slovenia
11751 Posts

Posted - 04/04/2006 :  14:21:51  Show Profile  Visit spirit1's Homepage
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 04/04/2006 :  14:25:49  Show Profile  Visit tkizer's Homepage
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

Slovenia
11751 Posts

Posted - 04/04/2006 :  16:13:22  Show Profile  Visit spirit1's Homepage
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 04/04/2006 :  16:16:24  Show Profile  Visit tkizer's Homepage
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

Slovenia
11751 Posts

Posted - 04/04/2006 :  16:18:46  Show Profile  Visit spirit1's Homepage
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 04/04/2006 :  16:20:14  Show Profile  Visit tkizer's Homepage
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

Slovenia
11751 Posts

Posted - 04/04/2006 :  16:26:08  Show Profile  Visit spirit1's Homepage
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 04/04/2006 :  16:32:36  Show Profile  Visit tkizer's Homepage
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
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000