使用order by时动态排序依据-加速

Question

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

我发现使用动态case语句列名进行排序会减慢速度--但是如果我硬编码order by，一切都没问题。

有没有一种方法可以加快动态排序的速度，而不是让整个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试图构建一个适合所有参数的执行计划。这意味着当你给它不同的参数时，它不会改变主意，也不会选择不同的索引。( different index部分很重要，因为对不适当的索引进行重新排序的负载可能非常高。)

要做到这一点，唯一的方法是生成一个新的执行计划，这涉及到动态SQL --这正是您想要避免的。

但是，动态SQL和SP_EXECUTESQL不一定是个坏主意。由于它也可以被参数化，正确的使用确实允许执行计划的重用，并且在处理这些类型的问题时可以非常有效。

你需要避免使用动态SQL有什么特别的原因吗？

唯一实际可行的工作是以不同的顺序多次写出查询，并选择要与T-SQL块一起使用的查询。这将允许优化器生成不同的执行计划。

Answer 2

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

• 在存储过程中构建动态查询并使用sp_executesql
• Build a dynamic query client

在实践中，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
 )

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