为什么临时表和子查询之间存在巨大的性能差异？

Question

这是一个关于SQL Server 2008 R2的问题。

我不是DBA，远非如此。我是一名Java开发人员，有时需要编写SQL（大多嵌入在代码中）。我想知道我是否做错了什么，如果是的话，我该怎么做才能避免再次发生。

Q1：

SELECT something FROM (SELECT * FROM T1 WHERE condition1) JOIN ...

Q1 包含14个连接。

Q2与Q1相同，只有一个例外。在执行(SELECT * FROM T1 WHERE condition1)之前，将其存储在临时表中。

这不是相关子查询。

Q2：

SELECT * INTO #tempTable FROM T1 WHERE condition1
SELECT something FROM #tempTable  JOIN ...

再次，14个连接。

现在让我困惑的是，Q1花费了超过2分钟的时间（为了避免缓存起作用而尝试了几次），而Q2（两个查询组合起来）只花费了2秒钟！是什么原因呢？

- Ward

---

我的猜测是，对于“SELECT * FROM T1 WHERE condition1”的预估行数非常不准确。将其材料化为“#tempTable”意味着SQL Server知道将返回多少行。您能发布实际执行计划的XML版本吗？ - Martin Smith

Answer 1

为什么不建议使用子查询？

无论你使用的是哪种数据库，数据库优化器并不能总是正确地优化这种带有子查询的查询。在这种情况下，优化器的问题在于选择正确的结果集连接方式。连接两个结果集有几种算法。算法的选择取决于一个结果集和另一个结果集中包含的记录数量。如果你连接的是两个实际的表（子查询不是实际的表），那么数据库可以通过现有的统计数据轻松确定两个结果集中数据的数量。如果其中一个结果集是子查询，那么要理解它返回多少条记录就非常困难。在这种情况下，数据库可能会选择错误的查询计划，从而导致查询性能的显著降低。

用临时表重写查询的目的是简化数据库优化器。在重写的查询中，所有参与连接的结果集都将是实际的表，并且数据库将轻松确定每个结果集的长度。这将允许数据库选择所有可能查询计划中最快的保证方式。此外，不管条件如何，数据库都将做出正确的选择。使用临时表重写的查询在任何数据库上都可以很好地运行，这对于开发可移植的解决方案尤其重要。此外，重写的查询更易于阅读、理解和调试。

需要注意的是，用临时表重写查询可能会导致一些减速，因为需要额外的开销来创建临时表。如果数据库在选择查询计划时不会出错，它将比新查询更快地执行旧查询。然而，这种减速总是可以忽略不计的。通常创建一个临时表只需要几毫秒的时间。也就是说，这种延迟对系统性能没有明显的影响，通常可以忽略不计。

重要！不要忘记为临时表创建索引。索引字段应包括连接条件中使用的所有字段。

- Karthik AMR

---

SQL Server 查询引擎内部会创建临时表，而你提供的原因并不总是正确的。它取决于许多其他因素，如索引、碎片化、统计信息等。 - AnandPhadke

在临时表上创建索引可以提高查询性能。 - nirupam

你的回答非常误导人，而且是错误的。创建临时表只应在某些情况下考虑使用：https://dev59.com/gp_ha4cB1Zd3GeqP7vZh?noredirect=1#comment72660694_42772428 - Saber

@Arvand……这并不是“错误”的，尽管我不同意这个建议。如果你仔细阅读，Karthik和我都建议在临时表上使用索引来提高性能。根据我的经验，问题几乎总是嵌套循环连接，而这些可以通过查询提示避免。我发现查询提示比大量的临时表更容易维护。 - Gordon Linoff

@GordonLinoff 第一段和第二段的结论是，子查询应该使用临时表进行重写，因为：“数据库可以通过可用的统计数据轻松确定两个结果集中的数据量”，这是一个错误的假设，可能会导致错误的结论。 - Saber

Answer 2

这里有很多问题需要解决，包括索引、执行计划等。测试和比较结果是解决问题的方法。

你可以看一下通常会出现的嫌疑对象——索引。查看执行计划并进行比较。确保 WHERE 子句使用了正确的索引。确保在 JOINs 中使用了索引。

这些答案肯定会对你有所帮助。

• 性能：子查询还是连接查询
• CTE、子查询和临时表之间存在速度差异吗？

- Yaroslav