This article is as per request from Application Development Team Leader of my company. His team encountered code where application was preparing string for ORDER BY clause of SELECT statement. Application was passing this string as variable to Stored Procedure (SP) and SP was using EXEC to execute the SQL string. This is not good for performance as Stored Procedure has to recompile every time due to EXEC. sp_executesql can do the same task but still not the best performance.
Previously:
Application:
Nesting logic to prepare variable OrderBy.
Database:
Stored Procedure takes variable OrderBy as input parameter.
SP uses EXEC (or sp_executesql) to execute dynamically build SQL.
This was taking big hit on performance. The issue was how to improve the performance as well as remove the logic of preparing OrderBy from application. The solution I came up was using multiple CASE statement. This solution is listed here in simple version using AdventureWorks sample database. Another challenge was to order by direction of ascending or descending direction. The solution of that issue is also displayed in following example. Test the example with different options for @OrderBy and @OrderByDirection.
Currently:
Database only solution:
USE AdventureWorks
GO
DECLARE @OrderBy VARCHAR(10)
DECLARE @OrderByDirection VARCHAR(1)
SET @OrderBy = 'State' ----Other options Postal for PostalCode,
---- State for StateProvinceID, City for City
SET @OrderByDirection = 'D' ----Other options A for ascending,
---- D for descending
SELECT AddressID, City, StateProvinceID, PostalCode
FROM person.address
WHERE AddressID < 100
ORDER BY
CASE WHEN @OrderBy = 'Postal'
AND @OrderByDirection = 'D'
THEN PostalCode END DESC,
CASE WHEN @OrderBy = 'Postal'
AND @OrderByDirection != 'D'
THEN PostalCode END,
CASE WHEN @OrderBy = 'State'
AND @OrderByDirection = 'D'
THEN StateProvinceID END DESC,
CASE WHEN @OrderBy = 'State'
AND @OrderByDirection != 'D'
THEN StateProvinceID END,
CASE WHEN @OrderBy = 'City'
AND @OrderByDirection = 'D'
THEN City END DESC,
CASE WHEN @OrderBy = 'City'
AND @OrderByDirection != 'D'
THEN City END
GO
Above modified query has improved performance for our stored procedure very much. Let me know if you are implementing similar logic using any other method.
Reference : Pinal Dave (https://blog.sqlauthority.com)
126 Comments. Leave new
So what I don’t understand is why the application isn’t sorting the information in what ever way the user of the application wants it to be sorted. The biggest cost of the above execution plan is the sort operation. .Net applications and reporting applications are capable of sorting data – and if the application is running into performance issues with large sets of data – perhaps the data sets should be reduced or aggregated in order to accommodate the performance issues.
Also, if you must do the sorting on the DB Server side, and it must be dynamic, why do 6 case statements in a row? Why not do 2 case statements such as
CASE @OrderBy WHEN ‘Postal’ THEN PostalCode
WHEN ‘City’ THEN City
WHEN ‘State’ THEN StateProvinceID ELSE AddressID END
Then you don’t have 6 case statements being evaluated everytime the Stored Proc runs. You’ll lower the overhead of the Order By Operation.
In order to get the direction of the sort just do 2 case statements. 1 for the ‘D’ and 1 for the ‘A’, and nest the other case statement within.
Sorting should generally be done on the db server, partly because of the massive computing power and speed of the server but mainly because there may be indexes that can be used.
However, there are valid cases where a data set (sometimes large) can be brought down to a workstation client or to a server side of a web app (or even to the web client — aka browser) where the programmer is using certain tools such as the amazing grid widgets I’ve seen from DevXPress or Infragistics, which can implement sorting, grouping, crosstab and even drill down either on the client or using built-in Ajax functionality to bring down subsets of a data large data set. This allows the user to repeatedly resort, regroup, crosstab or drill down without having to requery the database.
Also, one can do
SELECT ROW_NUMBER( ) OVER (ORDER BY CASE WHEN … etc…) AS rownum
, other columns …
FROM …
WHERE …
and can even split into groups with the row numbers starting over in each group, using
SELECT ROW_NUMBER( )
OVER (PARTITION BY … ,
ORDER BY CASE WHEN … etc…
)
, other columns …
FROM …
WHERE …
— and I believe you can even reference rownum in the WHERE clause, to limit to, say top 10 in each group, or at least you can do that if the main query is put into a CTE
and the WHERE filter is put into a
SELECT …
FROM CTE
WHERE…
why can’t you do
case when @OrderByDirection = ‘D’ THEN (CASE @OrderBy WHEN ‘Postal’ THEN PostalCode
WHEN ‘City’ THEN City
WHEN ‘State’ THEN StateProvinceID ELSE AddressID END) END DESC,
case when @OrderByDirection ‘D’ THEN (CASE @OrderBy WHEN ‘Postal’ THEN PostalCode
WHEN ‘City’ THEN City
WHEN ‘State’ THEN StateProvinceID ELSE AddressID END) END ASC
The only issue you’re gonna run into is a datatype conversion for the postal code and city, but you could CAST the StateProvinceID into a varchar and pad it if you really wanted to do the logic on the server and still keep proper sorting.
But I must admit, I’m not a fan of the dynamic sorting on the server side – I’d be interested in knowing how much data is actually being pulled across to the application layer where you’re seeing 60% gains. Sounds like the data could be aggrigrated higher or more criteria added to restrict the amount of data crossing the pipe in the first place. With 60% gains (not knowing what those gains really are – could be a few seconds for all I know) it sounds like the bottleneck is the application. An evaluation of the application code would probably fetch you 60% gains as well.
sorry, there’s supposed to be a in there for the second case statement, I didn’t test it or anything, I just wrote it in the comments text box
IT’s ok……Can u tell me on thing?
I have one stored Procedure, in which I have used multiple case statements in Where Clause…Will they be executed for each row in table…? COz….this is not expected….
Thanks
Are there any advantages in using CASE over IF in a stored procedure?
The SQL compiler only stored the execution plan of the last executed IF branch and therefore if the next execution happens on another IF branch, the Stored Procedure is recompiled for that branch – thereby inducing ‘dynamism’ to a pre-compiled SP.
Are there any advantages in using a CASE statement? Won’t the SQL compiler compile only the last executed CASE WHEN branch and store it in it’s execution plan? Like it does for the IF statement?
Pardon me if my knowledge of the concepts is off.
Has anyone found a solution to the conversion problem?
When the case statement references columns of various datatypes, SQL often complains of a conversion error.
This is annoying and frustrating.
Dynamic SQL sucks on security and performance.
ORDER BY
CASE –- VARCHAR types ascending
WHEN @OrderBy = ‘ProductNameAsc’ THEN ProductName
END ASC,
CASE –- VARCHAR types descending
WHEN @OrderBy = ‘ProductNameDesc’ THEN ProductName
END DESC,
CASE — INT types ascending
WHEN @OrderBy = ‘QuantityAsc’ THEN Quantity
END ASC,
CASE — INT types descending
WHEN @OrderBy = ‘QuantityDesc’ THEN Quantity
END DESC,
CASE — MONEY types ascending
WHEN @OrderBy = ‘PriceAsc’ THEN Price
END ASC,
CASE — MONEY types descending
WHEN @OrderBy = ‘PriceDesc’ THEN Price
END DESC,
CASE — Default order by
WHEN 1 = 1 THEN ProductName
END ASC
I’ve seen the same thing in ORDER OVER clauses, instead of in the ORDER BY at the end. Works with columns of different data types.
Has anyone used more than one field in the WHEN Clause:
i.e.
CASE WHEN @OrderBy = ‘Last’
THEN Lastname, FirstName END
When I use Lastname, FirstName’ in a SQL Where clause I do not get an error but if I put the same statement in a CASE statement, then I get an error that it does not like the comma between Lastname and FirstName. What am I misunderstanding?
Any help will be appreciated.
This fixed my problem. Thanks!
Did you resolve your problem, I am having the same problem
Read my post on: on January 28, 2010 at 12:33 am by Carl Soto
Concate those name
I have a query with 8 to 10 columns on which sorting need to be done dynamically, it is efficient to do with order by and case statements or individual if else blocks depending on the column selected for sorting.
Hello,
I would like to ask if there is a way to have multiple (consecutive) order fileds in one query. Example:
ORDER BY
CASE WHEN @OrderBy = ‘Option1’
THEN ORDER BY Field1 ASC, Field 2 DESC END,
CASE WHEN @OrderBy = ‘Option2’
THEN ORDER BY Field1 DESC, Field 2 DESC END,
This is obviously wrong but I need something like this.
KShah above has asked the same thing but I don’t seem to be able to find an answer.
Thanks in advance,
Vasilis
ORDER BY
CASE
WHEN @col=”name” AND @sort=”asc” THEN (RANK() OVER (ORDER BY Name, Status))
WHEN @col=”name” AND @sort=”desc” THEN (RANK() OVER (ORDER BY Name DESC, Status))
WHEN @col=”status” AND @sort=”asc” THEN (RANK() OVER (ORDER BY Status, Name))
WHEN @col=”status” AND @sort=”desc” THEN (RANK() OVER (ORDER BY Status DESC, Name))
WHEN @col=”” THEN (RANK() OVER (ORDER BY Name, Status))
END
Good..This solved my problem.
@Carl : How does this affect my performance than an ordinary ORDER BY clause?
Is there any other way to achive this?
rave: Keep it simple; if your users do not request a multi-column order by then I would not use the above solution. Another way would be to use Data binding throught Linq to SQL.
Thanks.
It Solved my Problem
Hi,
Any answers for Vasilis’ question? I’m actually looking for exactly the same answer.
Thanks!
–E
Try this…
ORDER BY
CASE WHEN @OrderBy = ‘Option1′
THEN ORDER BY Field1 ASC END, Field 3 ASC
CASE WHEN @OrderBy = ‘Option2′
THEN ORDER BY Field1 DESC, Field 2 DESC END,
Why is this such a bulky SQL statement? You can accomplish the same thing when you change your order by like:
ORDER BY
CASE
WHEN @OrderBy = ‘Postal’ AND @OrderByDirection = ‘D’
THEN PostalCode DESC
WHEN @OrderBy = ‘Postal’ AND @OrderByDirection != ‘D’
THEN PostalCode
WHEN @OrderBy = ‘State’ AND @OrderByDirection = ‘D’
THEN StateProvinceID DESC
WHEN @OrderBy = ‘State’ AND @OrderByDirection != ‘D’
THEN StateProvinceID
WHEN @OrderBy = ‘City’ AND @OrderByDirection = ‘D’
THEN City DESC
WHEN @OrderBy = ‘City’ AND @OrderByDirection != ‘D’
THEN City
END
and if you want to increase performance and readability (in my opinion) then do it this way:
ORDER BY
CASE @OrderByDirection
WHEN ‘D’
CASE @OrderBy
WHEN ‘Postal’
THEN PostalCode DESC
WHEN ‘State’
THEN StateProvinceID DESC
WHEN ‘City’
THEN City DESC
END
ELSE
CASE @OrderBy
WHEN ‘Postal’
THEN PostalCode
WHEN ‘State’
THEN StateProvinceID
WHEN ‘City’
THEN City
END
END
Just a suggestion.
Sorry didn’t realize it wouldn’t tab properly. /t I promise it’s easier to read when properly tabbed.
This logic does not work when you use distinct in your select statement.
Could you describer, How can we use this logic when we are using distinct in the select statement?
Thanks
Vasilis Please Trye this
order by
CASE
WHEN @Test1 = 1 THEN
CASE WHEN @NestedTest2 = 1 THEN Customer.CustomersOwnNumber ELSE Customer.CustomerNumber
END
END,
CASE WHEN @SortOrder = 2 THEN Customer.Balance END DESC, Customer.CustomerName,
CASE WHEN @SortOrder 1 and @SortOrder 2 THEN Customer.CustomerName End
Can’t use case in ORDER BY clause. I have 2 ways to solve this problem:
1. Use the dataview sort property to sort the data in your code.
2. Use IF/ELSE statements in SQL server to choose the appropriate query to use. Example:
IF @sort = ‘Balance’
SELECT * from Table ORDER BY Customer.Balance
ELSE IF @Sort = ‘CustomerName’
SELECT * FROM Table ORDER BY CustomerName
I use SQL Server 2000. I do not know if this was changed in 2005.
Hello Vasilis
I tried something like this ..let the case statement fall through
ORDER BY
CASE WHEN @OrderBy = ‘Option1′
THEN ORDER BY Field1 END ASC,
CASE WHEN @OrderBy = ‘Option1’
THEN ORDER BY Field2 END DESC,
CASE WHEN @OrderBy = ‘Option2′
THEN ORDER BY Field1 END DESC,
CASE WHEN @OrderBy = ‘Option2′
THEN ORDER BY Field 2 END DESC ,
This is obviously wrong but I need something like this.
KShah above has asked the same thing but I don’t seem to be able to find an answer.
Thanks in advance,
The answer to Vasilis´question:
Use the same case multiple times in a row:
ORDER BY
CASE WHEN @SortOrder=1 THEN Cards.Cardholder
END,
CASE WHEN @SortOrder=1 THEN Transactions.Date
END,
CASE WHEN @SortOrder=2 THEN Transactions.Date
END,
Transactions.Amount
This would in case 1 order by cardholders, date, amount
and in case 2 oder by date, amount
Is it possible to use the results of a case statement to create another case statement
Here’s what I use:
ORDER BY
CASE @OrderBy
WHEN ‘itemCategory’ THEN itemCategory
WHEN ‘item’ THEN item
END ASC,
CASE @OrderBy
WHEN ‘itemCategory DESC’ THEN itemCategory
WHEN ‘item DESC’ THEN item
END DESC
If you have different datatypes, don’t cast as varchar since then you would have to pad strings, just a pain. When you have multiple data types, just divide the case groupings by datatype. So all the string fields would be together, all the numeric, then all the dates
Example:
ORDER BY
CASE @OrderBy
WHEN ‘itemCategory’ THEN itemCategory
WHEN ‘item’ THEN item
END ASC,
CASE @OrderBy
WHEN ‘someInt1’ THEN someInt1
WHEN ‘someInt2’ THEN someInt2
END ASC,
CASE @OrderBy
WHEN ‘itemCategory DESC’ THEN itemCategory
WHEN ‘item DESC’ THEN item
END DESC
CASE @OrderBy
WHEN ‘someInt1 DESC’ THEN someInt1
WHEN ‘someInt2 DESC’ THEN someInt2
END DESC
We have a mixed environment where we needed to support SQL 2000 and SQL 2005. The other reason that helps for sorting on the server is we use paging on the server… If you get paged data and then sort in the code retrieving it, it only sorts the paged data, which is no good to anybody.
I program in .net, and the object data source / gridviews automatically pass the sort with ” DESC” as part of the string, so we checked the asc and desc @OrderBy separately as you can see.