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

Question

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

到目前为止，我还不是一名 DBA。我是一名java开发人员，时常需要编写SQL。 (主要嵌入在代码中)。我想知道我是否在这里做错了什么，如果是，我可以采取什么措施来避免再次发生这种情况。

第一个问题:

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

第一季度有 14 个加入

Q2 与 Q1 相同，但有一个异常(exception)。 (SELECT * FROM T1 WHERE condition1) 之前执行，并存储在临时表中。

这不是相关子查询。

第二季度:

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

再次有 14 人加入。

现在让我困惑的是，Q1花了> 2分钟，(尝试了几次，以避免缓存发挥作用)而Q2(两个查询加起来)花了2秒!给出了什么？

Answer 1

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

数据库优化器(无论您使用什么数据库)并不总是能够正确优化此类查询(带有子查询)。在这种情况下，优化器面临的问题是选择正确的方式来连接结果集。有多种用于连接两个结果集的算法。算法的选择取决于一个结果集中和另一个结果集中包含的记录数。如果连接两个物理表(子查询不是物理表)，数据库可以通过可用的统计信息轻松确定两个结果集中的数据量。如果结果集之一是子查询，那么了解它返回了多少条记录是非常困难的。这种情况下数据库可能会选择错误的join查询计划，从而导致查询性能急剧下降。

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

据了解，使用临时表重写查询可能会因额外费用(创建临时表)而导致速度减慢。如果数据库在选择查询计划时不会出错，那么它将比新查询更快地执行旧查询。然而，这种放缓总是可以忽略不计。通常，临时表的创建需要几毫秒的时间。也就是说，延迟不会对系统性能产生显着影响，通常可以忽略不计。

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