当表列相同时，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:
- 查询更具可读性(几乎总是如此).
- 性能得到改善.
和两者都是正确的:
- 该查询会产生语义上相同的结果，您可以通过足够的回归测试(包括所有边缘情况)来证明这一点.
- 性能不会降低(同样，在所有边缘情况下，还会发生环境变化，例如清除缓冲池，更新统计信息，清除计划缓存以及重新启动服务).
需要特别注意的是，随着JOIN变得越来越复杂和/或您依赖部分列中的重复项而不是其他列中的重复项，编写等效的EXCEPT查询可能是一个挑战.编写一个NOT EXISTS等效项，但其可读性比EXCEPT稍差一些，这要琐碎得多-而且通常会导致一个更好的计划(但请注意，除了在我刚才的方式).
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屋！
