当表列相同时,EXCEPT 的执行速度是否比 JOIN 快

2021-12-17 00:00:00 join performance sql sql-server except

要查找两个数据库之间的所有更改,我需要加入 pk 上的表并使用 date_modified 字段来选择最新记录.由于表具有相同的架构,因此使用 EXCEPT 会提高性能.我想用 EXCEPT 重写它,但我不确定 EXCEPT 的实现是否会在每种情况下执行 JOIN.希望有人对何时使用 EXCEPT 有更技术性的解释.

To find all the changes between two databases, I am left joining the tables on the pk and using a date_modified field to choose the latest record. Will using EXCEPT increase performance since the tables have the same schema. I would like to rewrite it with an EXCEPT, but I'm not sure if the implementation for EXCEPT would out perform a JOIN in every case. Hopefully someone has a more technical explanation for when to use EXCEPT.

推荐答案

没有人可以告诉你 EXCEPT 将永远或永远不会超过等效的 OUTER JOIN.无论您如何编写意图,优化器都会选择合适的执行计划.

There is no way anyone can tell you that EXCEPT will always or never out-perform an equivalent OUTER JOIN. The optimizer will choose an appropriate execution plan regardless of how you write your intent.

也就是说,这是我的指导方针:

That said, here is my guideline:

当至少有一个符合以下条件时,使用EXCEPT:

Use EXCEPT when at least one of the following is true:

  1. 查询更具可读性(这几乎总是正确的).
  2. 性能得到改善.

并且两种都正确:

  1. 查询产生语义相同的结果,您可以通过充分的回归测试来证明这一点,包括所有边缘情况.
  2. 性能不会下降(同样,在所有边缘情况下,以及环境变化,例如清除缓冲池、更新统计信息、清除计划缓存和重新启动服务).

<小时>

请务必注意,随着 JOIN 变得更加复杂和/或您部分依赖重复项,编写等效的 EXCEPT 查询可能是一项挑战列而不是其他列.编写一个 NOT EXISTS 等价物,虽然比 EXCEPT 可读性稍差,但完成起来应该容易得多——并且通常会导致一个更好的计划(但请注意,我永远不会说ALWAYSNEVER,除非我刚刚这样做).


It is important to note that it can be a challenge to write an equivalent EXCEPT query as the JOIN becomes more complex and/or you are relying on duplicates in part of the columns but not others. Writing a NOT EXISTS equivalent, while slightly less readable than EXCEPT should be far more trivial to accomplish - and will often lead to a better plan (but note that I would never say ALWAYS or NEVER, except in the way I just did).

在这篇博文中我至少展示了在一种情况下,EXCEPT 被正确构造的 LEFT OUTER JOIN 和等效的 NOT EXISTS 变体 超越.

In this blog post I demonstrate at least one case where EXCEPT is outperformed by both a properly constructed LEFT OUTER JOIN and of course by an equivalent NOT EXISTS variation.

相关文章