使用 order by 时的动态 order by - 加速

Question

我正在使用行号从存储过程获取分页结果。

我发现使用动态 case 语句列名称进行排序会减慢速度 - 但如果我按一切硬编码顺序就可以了。

有没有一种方法可以通过不使整个 sql 查询成为一个字符串并使用 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

问题在于 SQL Server 正在尝试构建一个执行计划来适应所有参数。这意味着当您给它不同的参数时，它不会改变主意并选择不同的索引。 （不同索引部分很重要，因为对不适当的索引重新排序的负载可能非常高。）

实现这种情况的唯一方法是生成新的执行计划，这涉及动态 SQL - 这正是您想要避免的。

但是；动态 SQL 和 SP_EXECUTESQL 不一定是坏主意。由于它也可以参数化，正确使用确实允许执行计划重用，并且可以非常有效地解决此类问题。

是否有特定原因需要避免动态 SQL？

唯一实际的解决办法是多次以不同的顺序编写查询，然后选择与 T 一起使用的查询-SQL IF 块。这将允许优化器生成不同的执行计划。

Answer 2

SQL Server 不支持单个查询根据参数值使用不同的索引。 AFAIK，有两种解决方案：

• 在存储过程中构建动态查询并使用 sp_executesql
• 构建动态查询客户端

在实践中，PHP 和 .NET 应用程序很难共享代码。或者甚至针对不同的 .NET 版本。这使得第一个选项成为更强的选择。

因此，最好的方法是使用动态 SQL。是的，这非常令人惊讶。 :)

Answer 3

您可以尝试的一件事是将两个 case 子句合并为一个 case - 像这样：

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

显然，如果某些潜在的排序值是字符串而不是数字，则这种方法不实用。

Answer 4

尝试多个 ROW_NUMBER 语句，以便减少每次排序的处理量

   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