David Kirkpatrick David Kirkpatrick - 1 month ago 8
SQL Question

SQL - Why does adding CASE to an ORDER BY clause drastically cut performance? And how can I avoid this?

I'm looking to improve the performance of one of our stored procedures and have come across something that I'm struggling to find information on. I'm by no means a DBA, so my knowledge of SQL is not brilliant.

Here's a simplified version of the problem:

If I use the following query -

SELECT * FROM Product
ORDER BY Name
OFFSET 100 ROWS
FETCH NEXT 28 ROWS ONLY


I get the results in around 20ms

However if I apply a conditional ordering -

DECLARE @so int = 1

SELECT * FROM Product
ORDER BY
CASE WHEN @so = 1 THEN Name END,
CASE WHEN @so = 2 THEN Name END DESC
OFFSET 100 ROWS
FETCH NEXT 28 ROWS ONLY


The overall request in my mind is the same, but the results take 600ms, 30x longer.

The execution plans are drastically different, but being a novice I've no idea how to bring the execution path for the second case into line with the the first case.

Is this even possible, or should I look at creating separate procedures for the order by cases and move choosing the order logic to the code?

NB. This is using MS SQL Server

hvd hvd
Answer

Gordon Linoff is right that this prevents an index from being used, but to expand a bit on that:

When SQL Server prepares execution of a query, it generates an execution plan. This is a query being compiled to steps that the database engine can execute. It's at this point, generally, that it looks at which indices are available for use, but at this point, parameter values are not yet known, so the query optimiser cannot see whether an index on name is useful.

The workarounds in his answer are valid, but I'd like to offer one more:

Add OPTION (RECOMPILE) to your query. This forces your query execution plan to be recompiled each time, and each time, the parameter values are known, and allows the optimiser to optimise for those specific parameter values. It will generally be a bit less efficient than fully dynamic SQL, since dynamic SQL allows each possible statement's execution plan to be cached, but it will likely be better than what you have now, and more maintainable than the other options.