sql-server - 在 ROW_NUMBER() OVER ( ORDER BY ) 中使用 case 语句会导致严重的性能问题

Question

使用 case 语句ROW_NUMBER() OVER (ORDER BY)会导致严重的性能问题。

我有两个查询，第一个在 5 秒内运行，第二个需要几分钟才能运行。我正在尝试使用传递给存储过程的值进行排序。请让我知道如何解决此问题。

查询 #1：

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

查询 #2：

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

标签： sql-serverperformancedynamicsql-order-by