TOP vs. bottom - 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

SQL Server 2000 Forums

SQL Server Development (2000)

TOP vs. bottom

Author

Topic

TimSinnott
Starting Member

48 Posts

Posted - 2002-10-04 : 16:11:44

Hi all,

I have a table with let's say 10,000 records. I want a query that will return the 5 newest records in ascending order.

A simplified example:
....
USE pubs
Select * FROM (
SELECT TOP 5 * From titles ORDER BY title_id DESC
)
AS t ORDER BY title_id
....

This works, but after reading BOL about TOP, it seems to me that my 10,000 record table is going to be sorted entirely in reverse order before the 5 records get selected out. That seems like a lot of work just to get the last 5 records.

Am I wrong about that? Also, is there anything like a BOTTOM?

Thanks in advance.

Tim

r937
Posting Yak Master

112 Posts

Posted - 2002-10-04 : 16:15:30

no such thing as BOTTOM, as far as i know

your approach is right, grab the TOP from a DESC subquery and resort those

for efficiency, you might want to declare a descending index on that column

rudy
http://rudy.ca/

TimSinnott
Starting Member

48 Posts

Posted - 2002-10-04 : 16:25:37

Thanks, Rudy. Yah, I hadn't thought of the descending index. I'll do that.

Tim

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-10-04 : 18:50:10

SQL is clever enough to search the ascending index ordered backward to get the last 5 so its just as quick as getting the top 5. The only real need for a desc ordered index (only available in 2000) is when you have a multi column (composite) index and you need the columns ordered in different directions.

To take your example and use pubs (I'm actually using bigpubs2000 that comes with SQL2000 Unleashed - great book - and it has about 10 times the rows that the normal pubs has thus it's a more effective demo since the normal pubs titles table fits on 1 database page), run the following with statistics io on and Show actual execution plan.


SELECT TOP 5 * From titles ORDER BY title_id ASC
SELECT TOP 5 * From titles ORDER BY title_id DESC

You will notice the same number of reads (2 assuming a clustered ascending index on title_id - in the normal pubs db the whole table fits on 1 page so its not much of an example but in bigpubs2000 it occupies 10 pages so its effective in demonstrating the same IO cost for the 2 statements) and if you examine the plan - the same cost - the difference is in how it uses the index.


SELECT TOP 5 * From titles ORDER BY title_id ASC
  |--Top(5)
       |--Clustered Index Scan(OBJECT:([bigpubs2000].[dbo].[titles].[UPKCL_titleidind]),
 ORDERED FORWARD)
 SELECT TOP 5 * From titles ORDER BY title_id DESC
  |--Top(5)
       |--Clustered Index Scan(OBJECT:([bigpubs2000].[dbo].[titles].[UPKCL_titleidind]),
 ORDERED BACKWARD)

HTH
Jasper Smith

TimSinnott
Starting Member

48 Posts

Posted - 2002-10-04 : 19:53:59

That's good news, Jasper! :) I feel better about that thing now.
Many thanks.

Tim

TimSinnott
Starting Member

48 Posts

Posted - 2002-10-04 : 20:02:32

I had considered first crudely limiting the records to the most recent several days to avoid such a long sort, like this:
....
WHERE CreationTime > (getdate()- 10)
....

and then doing the TOP.

Which do you think would be least costly,
(a) crudely limiting and then doing the TOP, or
(b) just do the TOP alone on all 10,000 records?

Thanks.

Tim

r937
Posting Yak Master

112 Posts

Posted - 2002-10-05 : 04:49:50

jasper, that's pretty sweet, thanks

tim, your crude method would require a date calculation, so if that datetime field isn't indexed, it has to scan the table... and if it is indexed, seems like TOP will work no matter whether it's forward or backward, but then the date calculation could work against you, since it now has to make sure the row still qualifies the WHERE condition even if it's in the top 5...

hope that made sense, it did to me

rudy

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-10-05 : 11:03:04

Tim , the second sort is carried out on the derived table returned by the inner select. The top operator in the inner select can access the index ordered backward. Please examine the execution plan of you statement in the first post - you'll see the sort is carried out AFTER the inner TOP select has already been evaluated. The only extra cost is the extra sort operator and this is pretty much all CPU and very cheap. Your statement is fine as it is

- no need to change anything. Please examine the query plan and the statistics io values and you'll see exactly what the optomiser is doing. I hope this is clear.

HTH
Jasper Smith

TimSinnott
Starting Member

48 Posts

Posted - 2002-10-05 : 12:53:02

Thanks, Rudy and Jasper. It's good to hear that the easiest way is the best way (for once in my life

).

Jasper, I'm not too skilled at interpreting execution plans, but I can see now that it'll be useful to learn, so I guess I will.

Tim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources