为什么插入和连接 #temp 表更快？

Question

我有一个查询，看起来像这样：

SELECT
 P.Column1,
 P.Column2,
 P.Column3,
 ...
 (
   SELECT
       A.ColumnX,
       A.ColumnY,
       ...
   FROM
      dbo.TableReturningFunc1(@StaticParam1, @StaticParam2) AS A
   WHERE
      A.Key = P.Key
   FOR XML AUTO, TYPE  
 ),
 (
   SELECT
       B.ColumnX,
       B.ColumnY,
       ...
   FROM
      dbo.TableReturningFunc2(@StaticParam1, @StaticParam2) AS B
   WHERE
      B.Key = P.Key
   FOR XML AUTO, TYPE  
 )
FROM
(
   <joined tables here>
) AS P
FOR XML AUTO,ROOT('ROOT') 

P有大约5000行数据，A和B各有约4000行。

这个查询的运行时间大约需要10分钟以上。

然而，将它改为以下形式：

SELECT
 P.Column1,
 P.Column2,
 P.Column3,
 ...
INTO #P

SELECT
 A.ColumnX,
 A.ColumnY,
 ...
INTO #A     
FROM
 dbo.TableReturningFunc1(@StaticParam1, @StaticParam2) AS A

SELECT
 B.ColumnX,
 B.ColumnY,
 ...
INTO #B     
FROM
 dbo.TableReturningFunc2(@StaticParam1, @StaticParam2) AS B


SELECT
 P.Column1,
 P.Column2,
 P.Column3,
 ...
 (
   SELECT
       A.ColumnX,
       A.ColumnY,
       ...
   FROM
      #A AS A
   WHERE
      A.Key = P.Key
   FOR XML AUTO, TYPE  
 ),
 (
   SELECT
       B.ColumnX,
       B.ColumnY,
       ...
   FROM
      #B AS B
   WHERE
      B.Key = P.Key
   FOR XML AUTO, TYPE  
 )
FROM #P AS P
FOR XML AUTO,ROOT('ROOT')      

执行速度大约为4秒。

这似乎不太合理，因为插入到临时表中并执行连接应该默认成本更高。我认为 SQL 正在使用错误类型的子查询“连接”，但也许我错了，因为没有办法指定与相关子查询一起使用的连接类型。

是否有一种方法可以通过索引和/或提示来实现，而不使用 #temp 表/@table 变量呢？

编辑：请注意，dbo.TableReturningFunc1 和 dbo.TableReturningFunc2 是内联 TVF，而不是多语句或“参数化”视图语句。

- Joseph Kingry

Answer 1

在P的每一行中，您的过程都会被重新评估。

使用临时表所做的实际上是缓存存储过程生成的结果集，从而消除了重新评估的需要。

插入到临时表中很快，因为它不会生成redo/rollback。

由于有一个稳定的结果集，因此连接也很快，可以使用Eager Spool或Worktable创建临时索引。

您可以重用没有临时表的过程，使用CTE，但为了使其有效，SQL Server需要将CTE的结果具体化。

您可以尝试通过在子查询内使用ORDER BY来强制执行此操作：

WITH    f1 AS
        (
        SELECT  TOP 1000000000
                A.ColumnX,
                A.ColumnY
        FROM    dbo.TableReturningFunc1(@StaticParam1, @StaticParam2) AS A
        ORDER BY
                A.key
        ),
        f2 AS
        (
        SELECT  TOP 1000000000
                B.ColumnX,
                B.ColumnY,
        FROM    dbo.TableReturningFunc2(@StaticParam1, @StaticParam2) AS B  
        ORDER BY
                B.Key
        )
SELECT  …

这可能导致优化器生成Eager Spool。

然而，这并不是绝对可靠的。

确保的方法是在查询中添加OPTION (USE PLAN)，并将相应的CTE包装到Spool子句中。

请参阅我的博客中此条目的详细说明：

• 在子查询中生成XML

这种方法难以维护，因为每次重写查询时都需要重新编写计划，但这种方法效果很好且相当有效。

使用临时表会更容易些。

- Quassnoi

---

如果可能的话，我该如何在不显式地进行临时表操作的情况下实现这一点？ - Joseph Kingry

使用CTE的性能为1:28，临时表方法为0:04。比10多分钟好得多...但仍然有数量级的差异。 - Joseph Kingry

请问您能否发布您的存储过程，以便我可以检查性能？ - Quassnoi

评论已被锁定，但我已经添加了一个答案来展示一个替代方案，针对您在文章中演示的确切问题领域。 - RichardTheKiwi

Answer 2

这篇文章需要和Quassnoi的文章一起阅读。
http://explainextended.com/2009/05/28/generating-xml-in-subqueries/
通过合理使用CROSS APPLY，可以强制缓存或快捷评估内联TVFs。这个查询会立即返回结果。

SELECT  *
FROM    (
        SELECT  (
                SELECT  f.num
                FOR XML PATH('fo'), ELEMENTS ABSENT
                ) AS x
        FROM    [20090528_tvf].t_integer i
        cross apply (
            select num
            from [20090528_tvf].fn_num(9990) f
            where f.num = i.num
            ) f
) q
--WHERE   x IS NOT NULL -- covered by using CROSS apply
FOR XML AUTO

您没有提供真实的结构，因此很难构建有意义的内容，但该技术同样适用。

如果将Quassnoi文章中的多语句TVF更改为内联TVF，则计划甚至会更快（至少快一个数量级），并且计划神奇地简化为我无法理解的东西（它太简单了！）。

CREATE FUNCTION [20090528_tvf].fn_num(@maxval INT)  
RETURNS TABLE
AS RETURN 
        SELECT  num + @maxval   num
        FROM    t_integer  

统计学

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(10 row(s) affected)
Table 't_integer'. Scan count 2, logical reads 22, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 2 ms.

- RichardTheKiwi

Answer 3

这是一个关于子查询引用外部查询的问题，意味着子查询必须为外部查询中的每一行编译和执行。 您可以使用派生表来代替显式的临时表。 为了简化您的示例：

SELECT P.Column1,
       (SELECT [your XML transformation etc] FROM A where A.ID = P.ID) AS A

如果P包含10,000条记录，那么SELECT A.ColumnX FROM A where A.ID = P.ID将被执行10,000次。
相反，您可以使用派生表格，如下所示：

SELECT P.Column1, A2.Column FROM  
P LEFT JOIN 
 (SELECT A.ID, [your XML transformation etc] FROM A) AS A2 
 ON P.ID = A2.ID

好的，不是那种具有说明性的伪代码，但基本思想与临时表相同，只是SQL Server在内存中完成整个过程：首先选择“ A2”中的所有数据并在内存中构建一个临时表，然后进行联接。这样可以避免您自己选择TEMP。

为了给您提供另一个上下文中的原则示例，在该上下文中可能更容易理解。考虑员工和缺勤信息，您希望显示每位员工记录的缺勤天数。

糟糕的：（运行与数据库中员工数量一样多的查询）

SELECT EmpName, 
 (SELECT SUM(absdays) FROM Absence where Absence.PerID = Employee.PerID) AS Abstotal        
FROM Employee

好的：（仅运行两个查询）

SELECT EmpName, AbsSummary.Abstotal
FROM Employee LEFT JOIN
      (SELECT PerID, SUM(absdays) As Abstotal 
       FROM Absence GROUP BY PerID) AS AbsSummary
ON AbsSummary.PerID = Employee.PerID

- flytzen

---

这并不能完全解决我遇到的问题。使用LEFT OUTER JOINS并转换为使用FOR XML PATH（以获得正确的嵌套）后，运行时间为1:26。与临时表方法相比，其运行时间为0:04。 - Joseph Kingry

在这个时候，我会这样做：将查询分解成其组成部分，并在查询分析器中加载每个部分，选择“显示预计执行计划”。然后再对整个查询做同样的操作。它应该能够让你对哪一步骤很慢以及优化器如何解释你的请求有一个很好的了解。看看它是否是其中一个子组件或只有当它被全部放在一起时才会出现。寻找循环和扫描。最重要的是，你可能会发现其中一步几乎占用了所有执行时间 - 看看你是否可以优化它。 - flytzen

Answer 4

使用中间临时表可能加速查询的原因有几种，但在您的情况下最有可能的原因是被调用的函数（未列出）可能是多语句 TVF 而不是内联 TVF。多语句 TVF 对其调用查询的优化是不透明的，因此优化器无法确定是否存在重复使用数据或其他逻辑/物理运算符重新排序优化的机会。因此，每次包含查询应生成具有 XML 列的另一行时，它只能重新执行 TVF。

简而言之，多语句 TVF 会使优化器受挫。

通常的解决方案（按（典型）偏好顺序）为：

1. 将有问题的多语句 TVF 重写为内联 TVF
2. 将函数代码内联到调用查询中，或者
3. 将有问题的 TVF 数据转储到临时表中。这就是您所做的...

- RBarryYoung

---

我同意，除了 TVF 是内联的，基本上是参数化视图。会在问题中添加这个澄清。总的来说是个好建议。 - Joseph Kingry

Answer 5

我同意，临时表是一个好的概念。当表中的行数增加到例如 4000 万行，并且我想通过与其他表连接来更新表中的多个列时，在这种情况下，我总是更喜欢使用公共表达式在选择语句中使用 case 语句更新列，现在我的选择语句结果集包含已更新的行。使用 case 语句的 select 语句将 4000 万条记录插入临时表花费了我 21 分钟，然后创建索引花费了 10 分钟，因此我的插入和索引创建时间共计 30 分钟。然后我将使用临时表更新结果集与主表进行连接更新。更新 1000 万条记录中的 5 分钟，因此我对于 1000 万条记录的整体更新时间几乎需要 35 分钟，而公共表达式只需要 5 分钟。在这种情况下，我的选择是公共表达式。

- Kannan.C

Answer 6

考虑使用 WITH common_table_expression 结构来替代您现在使用的子查询或临时表，参见 http://msdn.microsoft.com/en-us/library/ms175972(SQL.90).aspx。

- Alex Martelli

Answer 7

这个做法并不太合理，因为插入到临时表中再进行连接的成本似乎默认情况下应该更高。使用临时表时，您明确指示Sql Server使用哪个中间存储。但是，如果将所有内容都放在一个大查询中，Sql Server将自行决定。实际上，差异并不是很大；归根结底，无论您是否将其指定为临时表，都会使用临时存储。

在您的情况下，临时表的工作速度更快，那么为什么不坚持使用它们呢？

- Andomar

Answer 8

如果在您的特定情况下，临时表被证明更快，那么您应该使用表变量。

这里有一篇关于它们之间差异和性能影响的好文章：

http://www.codeproject.com/KB/database/SQP_performance.aspx

- ScottE

---

在SQL 2005及以上版本中，临时表在绝大多数情况下与表变量一样快甚至更快。这是因为表变量无法拥有统计信息，所以对于查询优化器来说，它们似乎只有1行（查看查询计划）。因此，它们被错误地优化，并且在有显著超过10行的情况下往往表现不佳。本文使用类似于您引用的文章的测试，但展示了在2005年放入更多行时会发生什么。 - RBarryYoung

糟糕，这是文章链接：http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx - RBarryYoung

呵呵。实际上，它们都是同一篇文章！ - RBarryYoung

我也发现了这一点。与#temp表相比，使用表变量的性能不如#temp表。 - Joseph Kingry