SQL“WITH”性能和临时表（可能使用“查询提示”来简化）

Question

给出下面的示例查询（仅限简化示例）

DECLARE @DT int; SET @DT=20110717; -- yes this is an INT
WITH LargeData AS (
    SELECT * -- This is a MASSIVE table indexed on dt field
    FROM mydata
    WHERE dt=@DT
), Ordered AS (
    SELECT TOP 10 * 
        , ROW_NUMBER() OVER (ORDER BY valuefield DESC) AS Rank_Number
    FROM LargeData
)
SELECT * FROM Ordered

和...

DECLARE @DT int; SET @DT=20110717;

BEGIN TRY DROP TABLE #LargeData END TRY BEGIN CATCH END CATCH; -- dump any possible table.

SELECT * -- This is a MASSIVE table indexed on dt field
INTO #LargeData -- put smaller results into temp
FROM mydata
WHERE dt=@DT;

WITH Ordered AS (
    SELECT TOP 10 * 
        , ROW_NUMBER() OVER (ORDER BY valuefield DESC) AS Rank_Number
    FROM #LargeData
)
SELECT * FROM Ordered

两者都会产生相同的结果，这是基于字段数据的列表中的有限且排名的值列表。

当这些查询变得相当复杂时（更多的表、大量的条件、多级“with”表别名等），底部查询的执行速度比顶部查询快得多。有时速度会快 20 倍到 100 倍。

问题是...

是否有某种查询提示或其他 SQL 选项可以告诉 SQL Server 自动执行相同类型的优化，或者是否有其他格式涉及更简洁的方法（尽量保持格式尽可能类似于查询 1）？

请注意，对于这个示例来说，“排名”或辅助查询只是无用的，执行的实际操作实际上并不重要。

这正是我所希望的（或类似的，但想法我希望很清楚）。请记住，下面的这个查询实际上不起作用。

DECLARE @DT int; SET @DT=20110717;
WITH LargeData AS (
    SELECT * -- This is a MASSIVE table indexed on dt field
    FROM mydata
    WHERE dt=@DT
    **OPTION (USE_TEMP_OR_HARDENED_OR_SOMETHING) -- EXAMPLE ONLY**
), Ordered AS (
    SELECT TOP 10 * 
        , ROW_NUMBER() OVER (ORDER BY valuefield DESC) AS Rank_Number
    FROM LargeData
)
SELECT * FROM Ordered

编辑：重要的后续信息！

如果在子查询中添加

 TOP 999999999      -- improves speed dramatically

您的查询，其行为方式将与在先前查询中使用临时表类似。我发现执行时间几乎以完全相同的方式得到改善。这比使用临时表要简单得多，基本上就是我想要的。

但是，

 TOP 100 PERCENT    -- does NOT improve speed

不以相同的方式执行（您必须使用静态数字样式 TOP 999999999 ）

解释：

从我可以从两种格式的查询的实际执行计划中看出（原始格式具有正常的 CTE）每个子查询的 TOP 为 99999999）

普通查询将所有内容连接在一起，就好像所有表都在一个大规模查询中一样，这是预期的。过滤条件几乎应用于计划中的连接点，这意味着同时评估更多行并将其连接在一起。

在 TOP 999999999 的版本中，实际执行计划明确地将子查询与主查询分开，以便应用 TOP 语句操作，从而强制创建子查询的内存“位图”，然后将其连接到主查询询问。这似乎实际上完全符合我的要求，事实上，它甚至可能更高效，因为具有大量 RAM 的服务器将能够完全在内存中执行查询，而无需任何磁盘 IO。就我而言，我们有 280 GB 的 RAM，远远超出了实际使用的范围。

Answer 1

您不仅可以在临时表上使用索引，而且还允许使用统计信息和提示。我在 CTE 文档中找不到关于能够使用统计数据的参考资料，并且明确指出您不能使用提示。

当您有一个大型数据集时，即使您不使用索引（可能是因为它将使用统计信息来制定计划），在临时表和表变量之间进行选择，临时表通常也是最高效的方法，我可能会怀疑 CTE 的实现更像是表变量而不是临时表。

我认为最好的办法是看看执行计划有何不同，以确定它是否可以修复。

当您知道临时表性能更好时，您到底反对使用它吗？

Answer 2

问题在于，在第一个查询中，SQL Server 查询优化器能够生成查询计划。在第二个查询中，无法生成良好的查询计划，因为您要将值插入到新的临时表中。我的猜测是在您看不到的某个地方正在进行全表扫描。

您可能想要在第二个查询中执行的操作是将值插入到 #LargeData 临时表中，就像您已经做的那样，然后在“valuefield”列上创建非聚集索引。这可能有助于提高您的表现。

Answer 3

SQL 很可能针对错误的参数值进行优化。

有几个选项

1. 尝试使用选项（RECOMPILE）。这样做会产生一定的成本，因为它每次都会重新编译查询，但如果需要不同的计划，则可能是值得的。

2. 您还可以尝试使用 OPTION(OPTIMIZE FOR @DT=SomeRepresentatvieValue) 问题是您选择了错误的值。

请参阅 SQL 中的我闻到一个参数！服务器查询优化团队博客