本文介绍了当表列相同时,EXCEPT的执行速度是否比JOIN的执行速度快的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

要查找两个数据库之间的所有更改,我保留了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稍差一些,这要琐碎得多-而且通常会导致一个更好的计划(但请注意,除了在我刚才的方式).


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的执行速度快的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

10-19 08:50