LEFT INNER JOIN vs. LEFT OUTER JOIN - 为什么 OUTER 需要更长的时间? [英] LEFT INNER JOIN vs. LEFT OUTER JOIN - Why does the OUTER take longer?

问题描述

我们有以下查询.使用 LEFT OUTER 连接需要 9 秒来执行.将 LEFT OUTER 更改为 LEFT INNER 将执行时间减少到 2 秒，并且返回相同 的行数.由于正在处理 dbo.Accepts 表中相同数量的行，无论连接类型如何，为什么外部连接需要 3 倍的时间?

We have the query below. Using a LEFT OUTER join takes 9 seconds to execute. Changing the LEFT OUTER to an LEFT INNER reduces the execution time to 2 seconds, and the same number of rows are returned. Since the same number of rows from the dbo.Accepts table are being processed, regardless of the join type, why would the outer take 3x longer?

SELECT CONVERT(varchar, a.ReadTime, 101) as ReadDate,
       a.SubID,
       a.PlantID,
       a.Unit as UnitID,
       a.SubAssembly,
       m.Lot
  FROM dbo.Accepts a WITH (NOLOCK)
LEFT OUTER Join dbo.Marker m WITH (NOLOCK) ON m.SubID = a.SubID
WHERE a.LastModifiedTime BETWEEN @LastModifiedTimeStart AND @LastModifiedTimeEnd 
  AND a.SubAssembly = '400'

推荐答案

返回相同行数的事实是事后的，查询优化器无法提前知道 Accepts 中的每一行在 Marker 中都有匹配的行，可以吗?

The fact that the same number of rows is returned is an after fact, the query optimizer cannot know in advance that every row in Accepts has a matching row in Marker, can it?

如果连接两个表 A 和 B，假设 A 有 100 万行，B 有 1 行.如果你说 A LEFT INNER JOIN B 意味着只有匹配 A 和 B 的行才能产生结果，所以查询计划可以先扫描 B，然后使用索引在 A 中进行范围扫描，并可能返回 10 行.但是如果你说 A LEFT OUTER JOIN B 那么至少必须返回 A 中的所有行，所以无论在 B 中找到什么，计划都必须扫描 A 中的所有内容.通过使用 OUTER join 你正在消除一种可能的优化.

If you join two tables A and B, say A has 1 million rows and B has 1 row. If you say A LEFT INNER JOIN B it means only rows that match both A and B can result, so the query plan is free to scan B first, then use an index to do a range scan in A, and perhaps return 10 rows. But if you say A LEFT OUTER JOIN B then at least all rows in A have to be returned, so the plan must scan everything in A no matter what it finds in B. By using an OUTER join you are eliminating one possible optimization.

如果您知道 Accepts 中的每一行都将在 Marker 中匹配，那么为什么不声明一个外键来强制执行呢?优化器会看到约束，如果受信任，则会在计划中考虑它.

If you do know that every row in Accepts will have a match in Marker, then why not declare a foreign key to enforce this? The optimizer will see the constraint, and if is trusted, will take it into account in the plan.

这篇关于LEFT INNER JOIN vs. LEFT OUTER JOIN - 为什么 OUTER 需要更长的时间?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！