SQL Performance using ROW_NUMBER and dynamic order by

sql-server

Solution

How about this:

WITH data as (
    SELECT OrderID,
            ROW_NUMBER() OVER ( ORDER BY OrderID asc) as OrderID_ROW_ID,
            ROW_NUMBER() OVER ( ORDER BY Surname asc) as Surname_ROW_ID
        FROM Orders --WHERE X   
), CTE AS (               
        SELECT OrderID, OrderID_ROW_ID as ROW_ID
        FROM data
        where @SortCol = 'OrderID'

        union all

        SELECT OrderID, Surname_ROW_ID
        FROM data
        where @SortCol = 'Surname'
)
SELECT Orders.*, ROW_ID FROM CTE
INNER JOIN Orders ON CTE.OrderID = Orders.OrderID       
WHERE ROW_ID BETWEEN @RowStart AND @RowStart + @RowCount -1
order by ROW_ID
option (recompile);

Edit: Using `option (recompile)` on the example query in the post makes it go much quicker. However, `case` could not be used in exactly that way to choose between ascending/descending order.

The reason for this is that a plan is being generated for values of the variables that are inappropriate, and then this plan is cached. Forcing a recompile allows it to use the actual values of the variables.

Problem

I realise this is a common issue and has been discussed on SO previously, but I thought I would raise the question again in the hope that some viable alternative can be found. Take the following SQL which combines paging with dynamic ordering: ``` WITH CTE AS ( SELECT OrderID, ROW_NUMBER() OVER (ORDER BY CASE WHEN @SortCol='OrderID' THEN OrderID END ASC, CASE WHEN @SortCol='CustomerName' THEN Surname END ASC ) AS ROW_ID FROM Orders WHERE X ) SELECT Orders.* FROM CTE INNER JOIN Orders ON CTE.OrderID = Orders.OrderID WHERE ROW_ID BETWEEN @RowStart AND @RowStart + @RowCount -1; ``` As is well known, the ROW_NUMBER() method does not work well on large tables since the Indexes on the table cannot be properly used when using multiple CASE statements in the ORDER BY clause (see link). The solution we've been using for a number of years is to construct a string which is then executed using sp_executesql. Performance is good when using dynamic SQL like this but the resulting code is awful from a legibility point of view. I have heard of the ROWCOUNT method but as far as I'm aware it is still susceptible to the same issues when you introduce the dynamic order by element. So, at the risk of asking the impossible, what other options are there? EDIT In order to make some useful progress here I have put together three queries highlighting the various suggested methods: Current, Dynamic SQL solution (execution time 147ms) gbn Solution (execution time 1687ms) Anders Solution (execution time 1604ms) Muhmud Solution (execution time 46ms)

Original source