I hope the code sample below is well enough formatted and easy enough to
read. In short:

1. Creating a table of 250k random data in tempdb
2. Run a batch of two statements, with actual exectuion plan on.
   Execution plan tell these queries have the same cost, or very
   close to.
3. Run a batch of two loops, each running one of the above queries 25
   25 times, and showing that there are quite some differences in
   the cost of the queries.

Why is this happening? It looks like a bug to me.

Code follows.


-- ## 1
use tempdb;
go

create table Test (
  id integer identity,
  value float
);
go

-- Populate the table with 250k rows of random data
declare
  @a int;
set @a = 1;
while @a < 250000
begin
  insert into Test values (rand());
  set @a = @a+1;
end;

-- ## 2
-- Check the execution plan of these to statements
select top(10) value from Test order by value desc;

select value from (
  select value, row_number() over (order by value desc) as Rno from Test
) v where Rno <= 10;

-- ## 3
--Now, check the execution time of these two code blocks

--No 1
declare
  @a int,
  @start datetime;
set @a = 1;
set @start = getdate();
while @a < 25
begin
  select top(10) value from Test order by value desc;
  set @a = @a + 1;
end;
print cast(convert(int, cast(getdate()-@start as float)*24*360000) as
varchar(30))+ ' msec';

--No 2
set @a = 1;
set @start = getdate();
while @a < 25
begin
  select value from (
    select value, row_number() over (order by value desc) as Rno from
Test
  ) v where Rno <= 10;
  set @a = @a + 1;
end;
print cast(convert(int, cast(getdate()-@start as float)*24*360000) as
varchar(30))+ ' msec';

If you look closely execution plan is all abt the estimated cost. And well,
you can take the execution plan to be just a means to check if there isn't
some real bottleneck.. maybe for missing indexes, a table scan, a bookmark
lookup. Frankly the cost doesn't make much sense.. In fact you call any
function with a complex computation inside your query. The scalar function
cost will show as 0. So we cannot take it as is, but just as an aid to help
us resolve issues.

And now that its shown that execution plan cannot be taken as is.. You will
get the answer to your question if you run this query..

declare
  @a1 int,
  @start1 datetime;
set @a1 = 1;
set @start1 = getdate();

select top(10) value from Test order by value desc;
print cast(convert(int, cast(getdate()-@start1 as float)*24*360000) as
varchar(30))+ ' msec';

set @a1 = 1;
set @start1 = getdate();
select value from (
  select value, row_number() over (order by value desc) as Rno from Test
) v where Rno <= 10;
print cast(convert(int, cast(getdate()-@start1 as float)*24*360000) as
varchar(30))+ ' msec';

Without the loop.. you still have quite a difference.

And now, to the reason for this difference, because, the result set is
ordered and the rownumbers are comupted for all the rows though you are
selecting only the first 10, which goes into the "Sequence Project (compute
scalar)" in your execution plan, which as it says is a computational function
and has the cost 0 (or so it shows).


Hope this answers your question.

So, the conclusion is use execution plans to se what the SQL Server is
doing, but the costs are worthless. Good to know.

Ole Kristian Bangås (olekristian.ban***@masterminds.no) writes:
At least the total query cost is not very reliable. The actual costs fot
the steps are good, provided that you actually ran the query, I believe.

Also, it depends on how updated your statistics are. How volatile the table
is, and how old your execution plan is. None of it is important in this
particular case, though :)

Erland Sommarskog <esq***@sommarskog.se> wrote in
Well, I did run the queries, and checked actual execution plan. Version 2
used appoximately 4,5 times the time of version 1. The common steps in the
execution plan appears reliable, but the others not. According to the
actual execution plan they take no resources, but it takes noticably longer
to run, thus I got a bit confused.

Joining 2 select statement with like fields
Views and CTEs performance problem
Adding a space in a field
Can't use Shared Memory protocol to connect
merge two date ranges with overlap from two tables