I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 219, comments - 2287, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

Clustered Index seek and scan on selecting all data from a table

A select statement which returns all rows in nortwhwind..orders uses a clustered index scan.
That's great, since it has to go through all of the data... But look at this example:

use

go

northwind

set

declare

set

nocount on @i int , @time datetime @i = 0

set

@time = getdate()

while

@i<50

begin

 

 

select * from orders set @i = @i+1

end

set

@time = getdate() - @time

print

('iters scan:' + convert(varchar(20), @time, 114))

go

declare

set

@i int , @time datetime @i = 0

set

@time = getdate()

while

@i<50

begin

 

 

 

select * from orders where orderid > -1 set @i = @i+1

end

set

@time = getdate() - @time

print

('iters seek:' + convert(varchar(20), @time, 114))

set

nocount off
Run the queries on the sql server, unplug the server from the network
and have it running only QA or management studio.
Run each batch separatly for 10-20 times.
Calculate the average and compare them.
With enough iteration things like drawing to the screen, iterating @i and other non server things
have less impact on the average.

I get approx 10% better times from second batch (with where) than
with the first batch (without where).
It doesn't matter if i enumerate all columns instead of *.

So if CI seek is happening with where like this why isn't it happening without where?
We are getting back same data...
Is this an SQL server's optimizer "bug"?

EDIT: I tried it also with 
     DBCC DROPCLEANBUFFERS
after each select statement and it doesn't change results at all...

We discussed it on SQLTeam Forums in this post.

Print | posted on Tuesday, April 04, 2006 2:33 PM |

Feedback

Gravatar

# re: Clustered Index seek and scan on selecting all data from a table

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.
4/4/2006 7:36 PM | Tara
Gravatar

# re: Clustered Index seek and scan on selecting all data from a table

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 :)
4/4/2006 8:23 PM | Mladen
Gravatar

# re: Clustered Index seek and scan on selecting all data from a table

I agree that caching it wont make a difference. However, one thing I thought about is network traffic. I assume you ran this without the RJ45 plugged in / wireless device connected ?
4/4/2006 9:12 PM | Jon
Gravatar

# re: Clustered Index seek and scan on selecting all data from a table

yes i did it the way i explained.
4/4/2006 10:13 PM | Mladen
Gravatar

# re: Clustered Index seek and scan on selecting all data from a table

FWIW this is true in Oracle DB's too and is used all over the world as a brute force tuning approach
5/30/2006 11:40 PM | alf
Gravatar

# re: Clustered Index seek and scan on selecting all data from a table

CREATE TABLE #Test
(
v TINYINT
)

INSERT #Test
SELECT DISTINCT Number
FROM master..spt_values
WHERE Number BETWEEN 0 AND 255

CREATE UNIQUE INDEX IX_Test ON #Test (v)

select * from #test where v > -1
select * from #test where v >= 0

he WHERE does EXACTLY the same thing but look at the execution plans...
7/6/2006 3:26 PM | Peter Larsson
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET