Author |
Topic |
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-04-03 : 09:19:01
|
why doesselect * from orders make a clustered index scanand select * from orderswhere 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....logicalWHERE condition....and useful information provided in the WHERE clause....seek, cause you are filtering out at least some recordsAlso the SELECT * bit would influence the tendancy to go with a scan. |
|
|
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"] |
|
|
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 |
|
|
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 >0you could doselect * from table where id > -1and have better perfomance than simpleselect * from table where id > -1and 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"] |
|
|
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 |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-04-03 : 10:18:08
|
try it on northwind...i get a seek for > -1and about 5-10% better time.Go with the flow & have fun! Else fight the flow Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"] |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-03 : 11:19:28
|
"about 5-10% better time"How bizarre!Kristen |
|
|
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 ondeclare @i int , @time datetimeset @i = 0set @time = getdate()while @i<50begin select * from orders set @i = @i+1endprint('iters scan:' + convert(varchar(20), getdate() - @time, 114))set nocount offgoset nocount ondeclare @i int , @time datetimeset @i = 0set @time = getdate()while @i<50begin select * from orders where orderid > -1 set @i = @i+1endprint('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"] |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-04-03 : 15:53:27
|
Same results as Mladen.iters scan:00:00:02:017iters seek:00:00:01:860Seems the same on SQL2005 as well.Bizarre.rockmoose |
|
|
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"] |
|
|
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 seekactually took advantage on this one to optimize those nasty reports the dev team back thenthey 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
38200 Posts |
Posted - 2006-04-04 : 13:37:49
|
Here's my question posted on Mladen's blog:Don't you need to:DBCC FREEPROCCACHEDBCC DROPCLEANBUFFERSin 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 Kizeraka tduggan |
|
|
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 query2. 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"] |
|
|
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 Kizeraka tduggan |
|
|
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"] |
|
|
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 Kizeraka tduggan |
|
|
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"] |
|
|
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 Kizeraka tduggan |
|
|
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"] |
|
|
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 Kizeraka tduggan |
|
|
Next Page
|