Very slow ORDER BY - 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

Transact-SQL (2000)

Very slow ORDER BY

Author

Topic

jsmaycotte
Starting Member

1 Post

Posted - 2008-05-27 : 13:01:29

Hey there,

I'm having a little issue with a query. Basically I'm trying to do some paging. There's many ways to do it, I know, but what I came up with was this:

SELECT TOP 10 columnA, columnB
FROM table
WHERE columnA not in (
SELECT TOP 21190 columnA
FROM table
ORDER BY columnC DESC)
ORDER BY columnC DESC

Some notes about it:
columnA is the key
columnD, columnC and columnA are index1
columnD and columnA are index2
columnC is index3.
In SELECT TOP 21190, that number is pageNumber * pageSize

Now... it seems that the very last 'ORDER BY columnC DESC'is the problem. Like it is the query takes around 1 minute to run. If I take that line out, the query runs in less than a second. Or, if I change the ORDER BY to be done by columnA, it also runs in less than a second.

Any ideas?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-27 : 13:02:49

If you will be continually running this, you might want to consider having columnC DESC be the clustered index.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-27 : 13:26:43

Have you considered this method? I'm not sure how your NOT IN will perform. This may be better for first bunch of pages maybe not the last few...worth checking though :)


select	t.ColumnA, t.ColumnB
from	(--get just the target page rows
	select top 10 columnA
	from	(--get all rows through target page
		select top 21190 columnA from table order by columnC DESC
		) d1
	order by ColumnC ASC
	) d2
inner join Table t on t.ColumnA = d2.columnA
order by columnC DESC

EDIT:
you should probably add ColumnA to the end of your ORDER BYs for consistent results in the case of ties in your sort column

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources