How top actually works

#1

I'm coming from Oracle world and trying to find something similar to
rownum in Oracle. I know there exists TOP which normally if used in
the same select woth order by firstly sorts data and then only gets
top n. So the question is what actually happens when top is used in
inner query and order by in outer query. The problem is that it seems
to be somehow inconsistent at least for the first sight.

Using SQL Server 2005
So I have following test case:

create table t3 (id integer, data varchar(4000));
insert into t3 values (1, replicate('a', 4000));
insert into t3 values (2, replicate('b', 4000));
insert into t3 values (3, replicate('c', 4000));
insert into t3 values (4, replicate('d', 4000));
insert into t3 values (5, replicate('e', 4000));
insert into t3 values (6, replicate('f', 4000));
insert into t3 values (7, replicate('g', 4000));
insert into t3 values (8, replicate('h', 4000));
insert into t3 values (9, replicate('i', 4000));

SET STATISTICS IO ON
firstly just select all rows to know how many logical reads are needed
for all table.

select * from t3
1 aaa...
....
9 iiii....
logical reads 5

Now get first two rows without any where clause:
select top 2 * from t3
1 aaa...
2 bbb...
logical reads 1

Now the same first two rows just with outer select without any order
by:
select * from (
select top 2 * from t3
) as q
1 aaa...
2 bbb...
logical reads 1

OK till now it's as expected, just one logical read get first 2 rows
and end query.
However look at next query's logical reads 5. This somehow is very
interestingly equal to logical reads for select all rows from t3.

select * from (
select top 2 * from t3
) as q
order by data asc
1 aaa...
2 bbb...
logical reads 5

So the next one shows that order by clause has affected the result set
and actually semms to be pushed into inner query. Also logical reads
are 5 meaning that actually we have scanned all the table.

select * from (
select top 2 * from t3
) as q
order by data desc
9 iii..
8 hhh...
logical reads 5

However for TOP 1 everything works in a different way i.e. there is
always the same one row and the same one logical read in spite of
diffferent order by clauses:

select * from (
select top 1 * from t3
) as q
order by data asc
1 aaa....
logical reads 1

select * from (
select top 1 * from t3
) as q
order by data desc
1 aaa....
logical reads 1

So where is the truth? Why the functionality is different?

The business case is that we have search with potentially weak user
criteria resulting in BIG potential result sets, but we want to show
the user just ANY N rows satisfying criteria. But these N rows should
be ordered. So what I'd like to achieve is:
1) get ANY no more than N rows according to my criteria
2) sort these N rows according to my order by clause.

I DEFINITELY don't want:
1) get ALL rows
2) sort them and throw away all but first N.

TIA, Gints

#2

"Gints Plivna" <gints.plivna (AT) gmail (DOT) com> wrote

Quote:

I'm coming from Oracle world and trying to find something similar to
rownum in Oracle. I know there exists TOP which normally if used in
the same select woth order by firstly sorts data and then only gets
top n. So the question is what actually happens when top is used in
inner query and order by in outer query. The problem is that it seems
to be somehow inconsistent at least for the first sight.

Using SQL Server 2005
So I have following test case:

create table t3 (id integer, data varchar(4000));
insert into t3 values (1, replicate('a', 4000));
insert into t3 values (2, replicate('b', 4000));
insert into t3 values (3, replicate('c', 4000));
insert into t3 values (4, replicate('d', 4000));
insert into t3 values (5, replicate('e', 4000));
insert into t3 values (6, replicate('f', 4000));
insert into t3 values (7, replicate('g', 4000));
insert into t3 values (8, replicate('h', 4000));
insert into t3 values (9, replicate('i', 4000));

SET STATISTICS IO ON
firstly just select all rows to know how many logical reads are needed
for all table.

select * from t3
1 aaa...
...
9 iiii....
logical reads 5

Now get first two rows without any where clause:
select top 2 * from t3
1 aaa...
2 bbb...
logical reads 1

Now the same first two rows just with outer select without any order
by:
select * from (
select top 2 * from t3
) as q
1 aaa...
2 bbb...
logical reads 1

OK till now it's as expected, just one logical read get first 2 rows
and end query.
However look at next query's logical reads 5. This somehow is very
interestingly equal to logical reads for select all rows from t3.

select * from (
select top 2 * from t3
) as q
order by data asc
1 aaa...
2 bbb...
logical reads 5

So the next one shows that order by clause has affected the result set
and actually semms to be pushed into inner query. Also logical reads
are 5 meaning that actually we have scanned all the table.

select * from (
select top 2 * from t3
) as q
order by data desc
9 iii..
8 hhh...
logical reads 5

However for TOP 1 everything works in a different way i.e. there is
always the same one row and the same one logical read in spite of
diffferent order by clauses:

select * from (
select top 1 * from t3
) as q
order by data asc
1 aaa....
logical reads 1

select * from (
select top 1 * from t3
) as q
order by data desc
1 aaa....
logical reads 1

So where is the truth? Why the functionality is different?

The business case is that we have search with potentially weak user
criteria resulting in BIG potential result sets, but we want to show
the user just ANY N rows satisfying criteria. But these N rows should
be ordered. So what I'd like to achieve is:
1) get ANY no more than N rows according to my criteria
2) sort these N rows according to my order by clause.

I DEFINITELY don't want:
1) get ALL rows
2) sort them and throw away all but first N.

TIA, Gints

TOP n without ORDER BY is non-deterministic. Ie. you are telling SQL Server
to return ANY n rows from the table, which it will therefore do by whatever
method it finds convenient. Since a table is an unordered set of rows by
definition the only way to select a specific "top n" rows is to specify some
logical ordering. This is much the same as with rownum in Oracle, which is
not bound to any fixed ordering in the table.

See also the ROW_NUMBER() function, which is standard SQL and supported by
both Oracle and SQL Server.

Hope that helps.

--
David Portas

#3

Gints,

"How top actually works" cannot be determined with a table with only 9
rows in 48 KB.

I ran your example query

select * from (
select top 2 * from t3
) as q
order by data asc

and replaced "t3" with a 65 million row table (18 GB). It would complete
with only 4 logical reads. When I replaced "top 2" with "top 10" in the
inner query, it would complete after 4 logical reads.

Your table is so small, that even the simplest query plan is so cheap
that the optimizer seems to consider it useless to search for anything
better.

--
Gert-Jan

Gints Plivna wrote:

Quote:

I'm coming from Oracle world and trying to find something similar to
rownum in Oracle. I know there exists TOP which normally if used in
the same select woth order by firstly sorts data and then only gets
top n. So the question is what actually happens when top is used in
inner query and order by in outer query. The problem is that it seems
to be somehow inconsistent at least for the first sight.

Using SQL Server 2005
So I have following test case:

create table t3 (id integer, data varchar(4000));
insert into t3 values (1, replicate('a', 4000));
insert into t3 values (2, replicate('b', 4000));
insert into t3 values (3, replicate('c', 4000));
insert into t3 values (4, replicate('d', 4000));
insert into t3 values (5, replicate('e', 4000));
insert into t3 values (6, replicate('f', 4000));
insert into t3 values (7, replicate('g', 4000));
insert into t3 values (8, replicate('h', 4000));
insert into t3 values (9, replicate('i', 4000));

SET STATISTICS IO ON
firstly just select all rows to know how many logical reads are needed
for all table.

select * from t3
1 aaa...
...
9 iiii....
logical reads 5

Now get first two rows without any where clause:
select top 2 * from t3
1 aaa...
2 bbb...
logical reads 1

Now the same first two rows just with outer select without any order
by:
select * from (
select top 2 * from t3
) as q
1 aaa...
2 bbb...
logical reads 1

OK till now it's as expected, just one logical read get first 2 rows
and end query.
However look at next query's logical reads 5. This somehow is very
interestingly equal to logical reads for select all rows from t3.

select * from (
select top 2 * from t3
) as q
order by data asc
1 aaa...
2 bbb...
logical reads 5

So the next one shows that order by clause has affected the result set
and actually semms to be pushed into inner query. Also logical reads
are 5 meaning that actually we have scanned all the table.

select * from (
select top 2 * from t3
) as q
order by data desc
9 iii..
8 hhh...
logical reads 5

However for TOP 1 everything works in a different way i.e. there is
always the same one row and the same one logical read in spite of
diffferent order by clauses:

select * from (
select top 1 * from t3
) as q
order by data asc
1 aaa....
logical reads 1

select * from (
select top 1 * from t3
) as q
order by data desc
1 aaa....
logical reads 1

So where is the truth? Why the functionality is different?

The business case is that we have search with potentially weak user
criteria resulting in BIG potential result sets, but we want to show
the user just ANY N rows satisfying criteria. But these N rows should
be ordered. So what I'd like to achieve is:
1) get ANY no more than N rows according to my criteria
2) sort these N rows according to my order by clause.

I DEFINITELY don't want:
1) get ALL rows
2) sort them and throw away all but first N.

TIA, Gints

#4

On 17 Janv., 01:33, Gert-Jan Strik <so... (AT) toomuchspamalready (DOT) nl>
wrote:

Quote:

Your table is so small, that even the simplest query plan is so cheap
that the optimizer seems to consider it useless to search for anything
better.

OK I've also tried it with much bigger table and got the same results
as you. Let's hope optimizer will be smart enough to distinguish big
work from small work and in case of big work won't do order before
top

Gints

#5

"David Portas" <REMOVE_BEFORE_REPLYING_dportas (AT) acm (DOT) org> wrote

Quote:

See also the ROW_NUMBER() function, which is standard SQL and supported by
... SQL Server.

unfortunately only since MS SQL Server 2005.

Regards, Fritz

#6

Gints Plivna (gints.plivna (AT) gmail (DOT) com) writes:

Quote:

OK I've also tried it with much bigger table and got the same results
as you. Let's hope optimizer will be smart enough to distinguish big
work from small work and in case of big work won't do order before
top

Since you are on SQL 2005, any reason to not use row_number()? Then
you would have code that would run both on SQL Server and on Oracle?

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#7

On 18 Janv., 00:28, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:

Quote:

Gints Plivna (gints.pli... (AT) gmail (DOT) com) writes:
OK I've also tried it with much bigger table and got the same results
as you. Let's hope optimizer will be smart enough to distinguish big
work from small work and in case of big work won't do order before
top

Since you are on SQL 2005, any reason to not use row_number()? Then
you would have code that would run both on SQL Server and on Oracle?

I'm quite sure row_number won't help me in this case, because I'd like
to limit found number of rows. row_number actually must have order by
clause and ordering before limiting returned number of rows is the
thing I'd like to avoid.
And also this code will run only on SQL Server, so no need for
"portable SQL" (I'm BTW quite sceptical about such "portable SQLs"
generally, because usually it means code will be slow on all
databases

.

Gints

#8

Gints Plivna (gints.plivna (AT) gmail (DOT) com) writes:

Quote:

I'm quite sure row_number won't help me in this case, because I'd like
to limit found number of rows. row_number actually must have order by
clause and ordering before limiting returned number of rows is the
thing I'd like to avoid.

That can be achieved with row_number with some trickery. Consider:

with numbered AS (
select *, rn = row_number() OVER (order by x)
from (SELECT *, x = 'x' FROM Orders) as s
)
SELECT * FROM numbered WHERE rn < 20
ORDER BY CustomerID

Quote:

And also this code will run only on SQL Server, so no need for
"portable SQL" (I'm BTW quite sceptical about such "portable SQLs"
generally, because usually it means code will be slow on all
databases

.

Sorry, since you mentioned that you came from Oracle, I somehow drew
the conclusion that you were porting code.

I agree on your opinion on "portable SQL".

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#9

On 24 Janv., 01:17, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:

Quote:

Gints Plivna (gints.pli... (AT) gmail (DOT) com) writes:
I'm quite sure row_number won't help me in this case, because I'd like
to limit found number of rows. row_number actually must have order by
clause and ordering before limiting returned number of rows is the
thing I'd like to avoid.

That can be achieved with row_number with some trickery. Consider:

* *with numbered AS (
* *select *, rn = row_number() OVER (order by x)
* *from * (SELECT *, x = 'x' FROM Orders) as s
* *)
* *SELECT * FROM numbered WHERE rn < 20
* *ORDER BY CustomerID

Ahh that's real trickery!

OK I'll remember it just in case in the future I'll need something
like that!
Thanks!

Gints

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

How top actually works

comp.databases.ms-sqlserver comp.databases.ms-sqlserver

How top actually works - 01-16-2008 , 04:59 PM

Re: How top actually works - 01-16-2008 , 05:26 PM

Re: How top actually works - 01-16-2008 , 05:33 PM

Re: How top actually works - 01-17-2008 , 02:28 AM

Re: How top actually works - 01-17-2008 , 08:58 AM

Re: How top actually works - 01-17-2008 , 04:28 PM

Re: How top actually works - 01-23-2008 , 06:07 AM

Re: How top actually works - 01-23-2008 , 05:17 PM

Re: How top actually works - 01-24-2008 , 04:25 AM