Faster way to do Top N Order By Desc?

Question

Post reply

Faster way to do Top N Order By Desc?

Erin Ramsay

SSCrazy Eights

Points: 9728
More actions
October 13, 2010 at 11:52 am

#387998

Is there a faster way to get the Top 1000 rows sorted on
ORDER BY COALESCE(<possible null field>,1) DESC
The plan shows that this sort is taking up 86% of my plan percentage and I'd like to optimize it.
Thanks,
Don
October 14, 2010 at 8:49 am

This was removed by the editor as SPAM
October 14, 2010 at 11:26 am

This was removed by the editor as SPAM

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

dant12 SSCrazy Points: 2712 More actions · Answer 1

please post the entire query, and if possible some sample data

--
Thiago Dantas
@DantHimself

WayneS SSC Guru Points: 95460 More actions · Answer 2

Donalith (10/13/2010)
Is there a faster way to get the Top 1000 rows sorted on
ORDER BY COALESCE(<possible null field>,1) DESC
The plan shows that this sort is taking up 86% of my plan percentage and I'd like to optimize it.
Thanks,
Don

How large is your table? Do you realize that this is likely to require a full scan of the table (or an index that has this column) since this needs to be calculated for each row in order to determine what value to use.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Erin Ramsay SSCrazy Eights Points: 9728 More actions · Answer 3

The table is 2.5 million rows.

Following is the entire query. If you can suggest a better method I would appreciate it.

SELECT TOP 1000

OM.OutID,

OM.MNumber,

OM.SCode,

OM.LDateTime,

OM.Mess,

OM.CID,

OM.Cype,

COALESCE(OM.Op,dbo.LookupCarrierFunc(OM.MNumber)) Operator,

SCG.Gateway

FROM OutMess OM

INNER JOIN View_SCodeCarGate SCG ON SCG.CID = OM.Op AND SCG.SCode = OM.SCode

WHERE LDateTime < GETDATE() AND CID = COALESCE(@CID,CID)

ORDER BY COALESCE(OM.Pry,1) DESC

Field names changed to preserve NDA.

ps: I didn't write this query but I'll take any ideas before I re-write it.

Don

GSquared SSC Guru Points: 260824 More actions · Answer 4

Are there numbers in that column when it's non-Null? If so, do they include numbers below 1 (zero or negative or fractions)?

If it's numeric, and if 1 is lowest number, then you should be able to dump the coalesce function and might be able to speed up the query significantly.

Having the column in the coalesce as the leading edge of a covering index for the query will also have the data pre-sorted in the index, which might remove a lot of processing. You can define an index column as descending order. I'm not sure how well that will work with your Where clause and join math, but it might be worth trying.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Erin Ramsay SSCrazy Eights Points: 9728 More actions · Answer 5

How can I take out the coalesce when it's possible to have a null value there?

I'll look at the covering index though, that would be a good place to start.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Because null will be at then end of that sort which seems to be what you want to do.

The question is simply : "In the Pry column, is there any row that is < 1". If not, and that you are sure it'll remain that way, then the coalesce is "useless" and can be dropped.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

SELECT * FROM (

SELECT 1 AS a UNION ALL

SELECT 2 UNION ALL

SELECT NULL) dt

ORDER BY a DESC

SELECT * FROM (

SELECT 1 AS a UNION ALL

SELECT 2 UNION ALL

SELECT NULL) dt

ORDER BY COALESCE(a, 1) DESC

SELECT * FROM (

SELECT 1 AS a UNION ALL

SELECT 2 UNION ALL

SELECT 0 UNION ALL

SELECT NULL) dt

ORDER BY a DESC

SELECT * FROM (

SELECT 1 AS a UNION ALL

SELECT 2 UNION ALL

SELECT 0 UNION ALL

SELECT NULL) dt

ORDER BY COALESCE(a, 1) DESC

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 8

Also consider rewriting the scalar valued function to an inline table-valued function. As Mr. Ninja has already said, a scalar function used like this is a RBAR (row by agonizing row). If is executed once per row in the result, and if the function performs anything other than simple arithmetics, like performing a SELECT, you're potentially looking at a real performance killer.

Run a SQL Profiler trace with SP:StmtStarting to see what actually happens behind the scenes.

Erin Ramsay SSCrazy Eights Points: 9728 More actions · Answer 9

Thanks, Celko, I'll take a look at that structure as well.

Don

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 10

Donalith (10/14/2010)
How can I take out the coalesce when it's possible to have a null value there?
I'll look at the covering index though, that would be a good place to start.

The question is, where do you want the NULL's to occur? It looks like you want them to occur at the lowest number of 1. Is it ok if they occur where NULL is considered to be less than 1 as in the following?

SELECT *

FROM (

SELECT NULL UNION ALL

SELECT 1 UNION ALL

SELECT 2 UNION ALL

SELECT 3

) d (SomeValue)

ORDER BY SomeValue DESC

If so, just drop the COALESCE from the ORDER BY and you'll suddenly be able to use an index for blistering speed.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)