关于SQL Server optmation子查询与join的问题

为了满足你对这类事情的好奇心,最好的方法是启动管理工作室并查看执行计划。您还需要查看SQL事件探查器。正如我的一位教授所说:“编译器是最终的权威。”当您想了解SQL Server中查询的性能概要时,也有类似的方法。 看。

从这里开始,此答案已更新

实际的比较可能很有启发性。例如,在我刚刚做的测试中,我发现任何一种方法都可能产生最快的时间,这取决于查询的性质。例如,表单的查询:

Select F1, F2, F3 From Table1 Where F4='X' And UID in (Select UID From Table2)

生成了表1上的表扫描和表2上的索引扫描,然后是右半联接。

表单查询:

Select A.F1, A.F2, A.F3 From Table1 A inner join Table2 B on (A.UID=B.UID) 
  Where A.Gender='M'

生成了相同的执行计划,但有一个警告:这次哈希匹配只是一个简单的右联接。所以,首先要注意的是:执行计划 没有明显的不同。

但这些查询不是重复的,因为第二个查询可能返回多个相同的记录(表2中的每个记录一个)。令人惊讶的是,这次演出 子查询比内部联接快得多。 当数据集位于低位时(感谢Red Gate SQL数据生成器),内部联接是 慢40倍 . 我相当震惊。

好吧,一个真正的苹果对苹果怎么样? 这个 是匹配的内部联接-请注意删除重复项的额外步骤:

Select Distinct A.F1, A.F2, A.F3 From Table1 A inner join Table2 B 
  on (A.UID=B.UID) 
  Where A.Gender='M'

执行计划确实发生了变化,因为在内部联接之后还有一个额外的步骤。不过,奇怪的是,时间急剧下降,以至于两个查询几乎相同(在五分之二的测试中,内部连接速度稍快)。现在,我可以想象第一个内部联接(不带“distinct”)会稍微长一些,这是因为有更多的数据被转发到查询窗口——但它的数量只有原来的两倍(每个表1记录有两个表2记录)。我没有很好的解释为什么第一个内部连接要慢得多。

使用子查询向表2的搜索添加谓词时:

Select F1, F2, F3 From Table1 Where F4='X' And UID in 
    (Select UID From Table2 Where F1='Y')

然后将索引扫描更改为聚集索引扫描(这是有意义的,因为uid字段在我使用的表中有自己的索引),并且所用的时间百分比将增加。还添加了流聚合操作。当然,这会减慢查询速度。但是,当查询的第一次运行比随后的运行显示出更大的效果时,计划缓存显然开始了。

当您使用内部联接添加谓词时,整个计划会发生很大的变化(留给读者作为练习-这篇文章足够长)。同样,只要包含“distinct”,性能与子查询的性能基本相同。与第一个示例类似,省略distinct会显著增加完成时间。

最后一件事:有人建议(现在您的问题包括)查询表单:

Select Distinct F1, F2, F3 From table1, table2
Where (table1.UID=table2.UID) AND table1.F4='X' And table2.F1='Y'

此查询的执行计划与内部联接的执行计划类似(在表2的原始表扫描之后进行排序,并合并联接,而不是两个表的哈希联接)。两者的表现也相当。我可能需要一个更大的数据集来梳理差异,但到目前为止,我没有看到这个构造或“存在”构造有任何优势。

所有这些都被说出来了 -您的结果可能会有所不同。当我进行上述测试时,我几乎没有涉及到您可能遇到的所有查询。正如我在开头所说,SQL Server中包含的工具是您的朋友:使用它们。

那么:为什么选择一个而不是另一个呢?这实际上取决于您的个人偏好,因为就我测试的示例范围内的时间复杂性而言,内部连接到子查询似乎没有任何优势。

在大多数经典的查询案例中,我使用内部联接只是因为我和它们一起“长大”。不过,我确实在两种情况下使用子查询。首先,使用子查询更容易理解一些查询:表之间的关系是清单。第二个也是最重要的原因是,我经常处于从应用程序中动态生成SQL的位置,而子查询几乎总是更容易从代码中自动生成。

所以,关键是最好的解决方案就是 发展 最有效的。

使用 IN 更易读,我建议使用ANSI-92而不是ANSI-89连接语法:

SELECT DISTINCT S#
  FROM SHIPMENTS s
  JOIN PARTS p ON p.p# = s.p#
              AND p.color = 'Red'

检查您的解释计划,看看哪个更好,因为它取决于数据和表设置。

如果您没有从表中选择任何内容,我将使用exists子句。

SELECT DISTINCT S# 
FROM shipments a
WHERE EXISTS (SELECT 1
              FROM parts b
              WHERE b.color = ‘Red’
                AND a.P# = b.P#)

这将优化为与您发布的第二个相同。

SELECT DISTINCT S# 
FROM shipments,parts 
WHERE shipments.P# = parts.P# and parts.color = ‘Red’;

在中使用将强制SQL Server不在该列上使用索引,子查询通常速度较慢。