Dynamic order when using order - acceleration

Question

I use a line number to return the results from a stored procedure.

I find that ordering using the dynamic column name of the operator slows down, but if I hard-code the order, everything will be fine.

Is there a way to speed up the dynamic order by not making the WHOLE sql query a single line 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] 

Answer 1

The problem is that SQL Server is trying to build ONE execution plan to match all parameters. This means that it does not change it and does not select a different index when you give it different parameters. (The different index is important because the load of reassigning a non-local index can be very high.)

The only way to do this is to create a new execution plan and enable dynamic SQL - exactly what you want to avoid.

Nonetheless; dynamic SQL and SP_EXECUTESQL are not necessarily a bad idea. Since this can also be parameterized, proper use allows reuse of the execution plan and can be exceptionally effective in such problems.

Is there any specific reason why you need to avoid dynamic SQL?

The only realistic job is to repeatedly write a query with a different order and choose which one to use with T-SQL IF blocks. This will allow the optimizer to generate various execution plans.

Answer 2

When using a single query, SQL Server does not support different indexes based on parameter values. AFAIK, there are two solutions:

• Create a dynamic query in a stored procedure and use sp_executesql
• Creating a client part of a dynamic request

In practice, it is difficult to use PHP and a .NET application to share code. Or even for different versions of .NET. This makes the first option more powerful.

So the best way to do this is with dynamic SQL. And yes, this is pretty amazing. :)

Answer 3

One thing you could try would be to combine your two cases into one 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.

Answer 4

Try executing several ROW_NUMBER statements so that you have less processing in sorting.

  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