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
northwindset
declare
set
nocount on @i int , @time datetime @i = 0set
@time = getdate()while
@i<50begin
select * from orders set @i = @i+1end
set
@time = getdate() - @timeprint
('iters scan:' + convert(varchar(20), @time, 114))go
declare
set
@i int , @time datetime @i = 0set
@time = getdate()while
@i<50begin
select * from orders where orderid > -1 set @i = @i+1end
set
@time = getdate() - @timeprint
('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.