当表列相同时，EXCEPT 的执行速度是否比 JOIN 快 [英] Does EXCEPT execute faster than a JOIN when the table columns are the same

问题描述

要查找两个数据库之间的所有更改，我需要加入 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 可读性稍差，但完成起来应该容易得多——并且通常会导致一个更好的计划(但请注意，我永远不会说ALWAYS 或 NEVER，除非我刚刚这样做).

---

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.

这篇关于当表列相同时，EXCEPT 的执行速度是否比 JOIN 快的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！