mssql row_number() over dynamic order by performance issues

Question

Using case statement in ROW_NUMBER() OVER ( ORDER BY ) causes severe performance issues

I have two queries the first one runs in 5 seconds, the second one takes a couple of minutes to run. I am trying to sort using a value passed to a stored procedure. Kindly let me know how I can fix this issue.

ONE

select top(10) ROW_NUMBER() OVER (
ORDER BY POData.PurchaseOrderNumber
) AS ROW_NUM,
Received.QuantityReceived,POData.PurchaseOrderNumber,POData.*
from POData inner join Received on POData.Id = Received.PODataId

TWO

declare @OrderBy nvarchar(32) = 'PurchaseOrderNumber'
select top(10)
ROW_NUMBER() OVER (
ORDER BY
case when @OrderBy = 'PurchaseOrderNumber' then POData.PurchaseOrderNumber end
) AS ROW_NUM,
Received.QuantityReceived,POData.*
from POData inner join Received on POData.Id = Received.PODataId

Answer 1

Try this:

declare @OrderBy nvarchar(32) = 'PurchaseOrderNumber'

select top(10)
*,
ROW_NUMBER() OVER (
ORDER BY OrderBy
) AS ROW_NUM
FROM (
SELECT Received.QuantityReceived,POData.*, OrderBy = case when @OrderBy = 'PurchaseOrderNumber' then POData.PurchaseOrderNumber end
from POData inner join Received on POData.Id = Received.PODataId
) a

Answer 2

I would try adding this hint at the end:

OPTION (RECOMPILE)

This permits SQL Server to handle the variable as a constant, and select the best plan for the given value.

Answer 3

Adding to the other answers, if your intent is to return results ordered by ROWNUM, you need to add ORDER BY ROWNUM to the end of the outer query. Results might be returned in the desired order without it but the behavior is not guaranteed.

Also be aware the ROWNUM value will be arbitrary when the CASE expression returns NULL.

Answer 4

Hi @Murugan Andezuthu Dharmaratnam ，

Please refer to:

declare @OrderBy nvarchar(32) = 'PurchaseOrderNumber'  
;with cte  
as(select ROW_NUMBER() OVER (ORDER BY POData.PurchaseOrderNumber) AS ROW_NUM,  
Received.QuantityReceived,POData.*  
from POData inner join Received on POData.Id = Received.PODataId  
where @OrderBy = 'PurchaseOrderNumber'  
union all  
select ROW_NUMBER() OVER (ORDER BY (select distinct null from POData)) AS ROW_NUM,  
Received.QuantityReceived,POData.*  
from POData inner join Received on POData.Id = Received.PODataId  
where @OrderBy<>'PurchaseOrderNumber')  
  
select top(10)* from cte  
order by ROW_NUM  
option (recompile)  

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.