Dynamic order by when using order by - speeding up

Question

I am using row number to get paged results back from a stored procedure.

I am finding that ordering using a dynamic case statement column name is slowing things down - but if I hardcode the order by everything is ok.

Is there a way to speed up the dynamic order by without making the WHOLE sql query one string and using SP_EXECUTESQL

            ROW_NUMBER() OVER (ORDER BY 
            CASE WHEN @OrderByColumnName = 'IdentityValue' AND @OrderAscending  = 0 THEN CLH.IdentityValue END DESC,
            CASE WHEN @OrderByColumnName = 'IdentityValue' AND @OrderAscending  = 1 THEN CLH.IdentityValue END
            --CLH.CustomerName

            ) AS [ROW]

score 1 · Accepted Answer · answered Dec 13 '11 at 12:39

The problem is that SQL Server is trying to build ONE execution plan to fit all parameters. This means that it doesn't change it's mind and pick a different index when you give it different parameters. (The different index part is important as the load of re-ordering an inappropriate index can be very high.)

The only way for that to happen is to generate a new execution plan, and that involves dynamic SQL - Exactly what you want to avoid.

However; dynamic SQL and SP_EXECUTESQL is not necessarily a bad idea. As it can also be parametrised, correct use does allow execution plan re-use and can be exceptionally efficient at these kinds of problems.

Is there specific reason that you need to avoid dynamic SQL?

The only realistic work around is to have the query written out multiple times, with the different ordering, and pick which one to use with T-SQL IF blocks. This will allow the optimiser to generate the varying execution plans.

score 1 · Answer 2 · answered Dec 13 '11 at 12:57

Having a single query use different indexes based on parameter values is not supported in SQL Server. AFAIK, there are two solutions to this:

Build a dynamic query in a stored procedure and use sp_executesql
Build a dynamic query client side

In practice, it's hard for a PHP and a .NET application to share code. Or even for different .NET versions. That makes the first option the stronger choice.

So the best way to do this involves dynamic SQL. And yeah that's pretty surprising. :)

score 0 · Answer 3 · answered Dec 13 '11 at 12:56

One thing you could try would be to combine your two case clauses into a single case - like this:

(ORDER BY 
        CASE WHEN @OrderByColumnName = 'IdentityValue' AND @OrderAscending  = 0 
                 THEN CLH.IdentityValue*-1
             WHEN @OrderByColumnName = 'IdentityValue' AND @OrderAscending  = 1 
                 THEN CLH.IdentityValue 
        END
 )

Obviously, this approach would not be practical if some of your potential sort values are strings instead of numbers.

The OP says "ordering using a dynamic case statement column name is slowing things down - but if I hardcode the order by everything is ok". That's probably because of indexes. A `case` in the order by also does not allow the use of indexes. — Andomar, Dec 13 '11 at 12:58

score 0 · Answer 4 · answered Dec 13 '11 at 14:50

Try multiple ROW_NUMBER statements so you have less processing per sort

   ROW_NUMBER() OVER (ORDER BY CLH.IdentityValue) DESC AS rnDesc,
   ROW_NUMBER() OVER (ORDER BY CLH.IdentityValue) AS rnAscDesc,
   ...
ORDER BY
   CASE WHEN @OrderByColumnName = 'IdentityValue' AND @OrderAscending  = 0 THEN rnDesc END,
   CASE WHEN @OrderByColumnName = 'IdentityValue' AND @OrderAscending  = 1 THEN rnDesc END

Dynamic order by when using order by - speeding up

4 Answers4