Sql Server - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

The Yak Corral

Sql Server

Author

Topic

Stoad strehngthend
Yak Posting Veteran

54 Posts

Posted - 2008-08-16 : 17:16:44

what is TopN Sort in Est. Exec. Plan?
cmp plans of (and ad libitum their exec. times):
select top 100000 fie from t order by fie
and
select top 10 percent fie from t order by fie

assume in "t" 1 mln. records

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-08-16 : 17:46:37

Top N Sort is similar to the Sort iterator, except that only the first N rows are needed, and not the entire result set. For small values of N, the SQL Server 2005 query execution engine attempts to perform the entire sort operation in memory.

Stoad strehngthend
Yak Posting Veteran

54 Posts

Posted - 2008-08-16 : 17:58:33

and what's the diff. with the 2nd case? In essence the both queries are the same, but only the 1st uses TopN sort
the 2nd uses "plain" Sort (whatever it means)
also I forgot to mention: NO any indexes on field "fie". It's an abs. req

Stoad strehngthend
Yak Posting Veteran

54 Posts

Posted - 2008-08-16 : 18:01:47

for those who unwilling to experiment on their own:
the 1st select runs by ~4 times of the 2nd's exec. time

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-08-16 : 18:56:10

http://blogs.msdn.com/craigfr/archive/2007/08/01/more-on-top.aspx

elsasoft.org

Stoad strehngthend
Yak Posting Veteran

54 Posts

Posted - 2008-08-16 : 20:37:48

thanx; but why does it stubbornly choose the wrong (slow TopN Sort) plan?

Stoad strehngthend
Yak Posting Veteran

54 Posts

Posted - 2008-08-16 : 20:41:18

[code]
/*
set nocount on

create table t (f varchar(50) COLLATE Latin1_General_BIN NOT NULL)

declare @i int
set @i = 1

begin tran
while @i <= 500000
begin
insert into t select newid()
set @i = @i + 1
end
commit tran
*/

declare @dt datetime

set @dt = getdate()
select f into t1 from (select top 80 percent f from t order by f) z
select datediff(ms, @dt, getdate()) , 'ms -- top 80%'

set @dt = getdate()
select f into t2 from (select top 400000 f from t order by f) z
select datediff(ms, @dt, getdate()) , 'ms -- top 400000 = top 80%'

set @dt = getdate()
select f into t3 from (select top 100 percent f from t order by f) z
select datediff(ms, @dt, getdate()) , 'ms -- top 100%'

set @dt = getdate()
select f into t4 from (select f from t) z
select datediff(ms, @dt, getdate()) , 'ms -- all rows, without ordering'

----------- -------------
5203 ms -- top 80%

----------- --------------------------
19610 ms -- top 400000 = top 80% <<<<<<<<<< ooch! <<<<<<<<<<<

----------- --------------
6453 ms -- top 100%

----------- --------------------------------
940 ms -- all rows, without ordering
[/code]
"select into" here just for to avoid outputting to the screen

Stoad strehngthend
Yak Posting Veteran

54 Posts

Posted - 2008-08-17 : 08:15:19

Means, SS uses for "top N" and "top N percent" different sorting algos.
And its engine doesn't differentiate "top 5" from "top 499999" but should do it.
Should be smth like this pseudo-code:

if (N / Number_of_all_rows_in_the_table <= 10%) then
use Sorting_Algo_#1
else
use Sorting_Algo_#2
end if

It does make sense. Consider a silly boundary case: "top 1" (= "min()")
it requires only one iteration over all rows; no need to sort the table, at all

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-08-17 : 21:43:30

Is statistics on the table up to date?

Stoad strehngthend
Yak Posting Veteran

54 Posts

Posted - 2008-08-18 : 13:54:22

Nothing to update here : there is no indexes (on purpose) on the column

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources