October 15, 2009 at 10:49 am
Hi,
I've a query that I'd like to order by 14 different clauses, so I send a variable @order and use the ORDER BY with a CASE clause:
DECLARE @order INT= 1
SELECT a.idAd ,
ROW_NUMBER() OVER
(
ORDER BY
CASE WHEN @order = -1 THEN a.publicKey END DESC,
CASE WHEN @order = -2 THEN c.name END DESC,
CASE WHEN @order = -3 THEN html.description END DESC,
CASE WHEN @order = -4 THEN r.region END DESC,
CASE WHEN @order = -5 THEN a.dateCreation END DESC,
CASE WHEN @order = -6 THEN a.numberOfPhotos END DESC,
CASE WHEN @order = -7 THEN u.smallName END DESC,
CASE WHEN @order = 1 THEN a.publicKey END ,
CASE WHEN @order = 2 THEN c.name END ,
CASE WHEN @order = 3 THEN html.description END ,
CASE WHEN @order = 4 THEN r.region END ,
CASE WHEN @order = 5 THEN a.dateCreation END ,
CASE WHEN @order = 6 THEN a.numberOfPhotos END,
CASE WHEN @order = 7 THEN u.smallName END
) AS row
FROM AdsMain a INNER JOIN .....
This code takes approximately 60 secs to return the results. If I use only 2 CASE clauses it takes 10 secs... As I add more CASE clauses it starts to take more time to execute... Each clause individually takes 6 secs except for the @order = 6 that takes 40 secs...
If I replace all the clauses by
CASE WHEN @order = 6 THEN a.numberOfPhotos
ELSE a.publicKey END
and @order = 1 it takes 36 secs, and with only ORDER BY a.publicKey it takes 6 secs...
As @order is 1 shouldn't it only take 6 secs?! I seems like SQL Server is using all the ORDER BY clauses and returning only the desired one....
Is this possible?! Is this a "feature" AKA "BUG" in SQL?
PS: The server where I tested this doesn't have SP1 yet!!
Thanks,
Pedro
October 15, 2009 at 2:42 pm
Hi,
Tried with SQL Server 2008 Enterprise x64 with SP1 and still the same behavior.
Seems like it makes all the ORDER's clause and return the desired one.
Pedro
October 15, 2009 at 8:19 pm
We are going to need the datatypes of all of these columns first.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 15, 2009 at 8:23 pm
I would also be curious how much faster that gets if you were to make that whole statement dynamic, using the CASE statement to plop the column name directly into the query.
Something like:
declare @sql varchar(max)
set @sql= 'DECLARE @order INT= 1
SELECT a.idAd ,
ROW_NUMBER() OVER
(
ORDER BY '+
CASE @order WHEN -1 THEN 'a.publicKey DESC'
WHEN -2 THEN 'c.name DESC'
WHEN -3 THEN 'html.description DESC'
WHEN -4 THEN 'r.region DESC'
WHEN -5 THEN 'a.dateCreation DESC'
WHEN -6 THEN 'a.numberOfPhotos DESC'
WHEN -7 THEN 'u.smallName DESC'
WHEN 1 THEN 'a.publicKey'
WHEN 2 THEN 'c.name'
WHEN 3 THEN 'html.description'
WHEN 4 THEN 'r.region'
WHEN 5 THEN 'a.dateCreation'
WHEN 6 THEN 'a.numberOfPhotos'
WHEN7 THEN 'u.smallName'
END +' ) AS row
FROM AdsMain a INNER JOIN .....'
EXEC(@SQL)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 15, 2009 at 11:55 pm
Hi,
I've already tried the dynamic SQL query and it takes 2 secs for each possible value in @order.
But shouldn't the CASE in the ORDER BY work the same way?
Thanks,
Pedro
October 16, 2009 at 1:36 am
A rise in execution time should be expected as you are raising the amount of columns sql server is having to order on, granted the majority will be null but it still has to order.
Did you see the difference between yours and Matt's query ?, it only needs to order on 1 column.
October 16, 2009 at 2:16 am
If I have
SELECT CASE WHEN @order = 1 THEN a.publicKey, WHEN @order = 2 THEN a.creationDate, WHEN @order = 3 THEN c.username ELSE a.idAd END FROM AdsMain INNER ....
it only returns one column... Why does the ORDER BY CASE ... has to order by all CASE clauses and, in the end, use only one!??! This doesn't seem like a "normal" behavior....
October 16, 2009 at 2:26 am
The issue is not how much data is returned , but how much work SQLServer has to do to return one column,
If @Order = -2 then this ....
ORDER BY
CASE WHEN @order = -1 THEN a.publicKey END,
CASE WHEN @order = -2 THEN c.name END,
CASE WHEN @order = -3 THEN html.description END
will equate to (assumming 3 rows x,y and z in c.name)
NULL,'x',NULL
NULL,'y',NULL
NULL,'z',NULL
ie 3 columns that are ordered on...
whereas
ORDER BY
CASE @order = when -1 THEN a.publicKey
WHEN -2 THEN c.name
WHEN -3 THEN html.description END
Will result in
'x',
'y',
'z'
Just because you think the nulls are unimportant doesnt mean that they dont have to be ordered on.
October 16, 2009 at 2:48 am
Hi,
Thanks for the explanation... very helpful..
If I use
select ROW_NUMBER() OVER (ORDER BY
CASE
WHEN @o = 1 THEN publicKey
WHEN @o = 2 THEN idAd
WHEN @o = 3 THEN region1
WHEN @o = 4 THEN region2
ELSE
region3
END)
FROM adsMain
it returns an error cause the columns haven't got the same data type... understandable.
So, definitely, the dynamic SQL is the best option for this case or is there any other away of doing this in SQL 2008?
Thanks,
Pedro
October 16, 2009 at 3:01 am
If you are going to use DynamicSql then use as Matt suggested , building a sql statement with only one order by condition.
See this link
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy