使用 JOIN 或使用 EXISTS 可以获得更好的性能吗? [英] Can I get better performance using a JOIN or using EXISTS?

问题描述

我有两个表制度和结果，我想看看是否有任何机构的结果，这样我就可以排除没有结果的机构.

I have two tables Institutions and Results and I want to see if there are any results for institutions that way I can exclude the ones that don't have results.

我能否使用 JOIN 或 EXISTS 获得更好的性能?

Can I get better performance using a JOIN or using EXISTS?

谢谢，
-尼梅什

Thank you,
-Nimesh

推荐答案

根据语句、统计信息和 DB 服务器，它可能没有区别 - 可能会产生相同的优化查询计划.

Depending on the statement, statistics and DB server it may make no difference - the same optimised query plan may be produced.

数据库在幕后连接表的方式基本上有 3 种:

There are basically 3 ways that DBs join tables under the hood:

• 嵌套循环 - 对于一个比第二个大得多的表.较小表中的每一行都会针对较大表中的每一行进行检查.

• Nested loop - for one table much bigger than the second. Every row in the smaller table is checked for every row in the larger.

Merge - 用于相同排序顺序的两个表.两者都按顺序运行并在它们对应的地方匹配.

Merge - for two tables in the same sort order. Both are run through in order and matched up where they correspond.

哈希 - 其他一切.临时表用于建立匹配.

Hash - everything else. Temporary tables are used to build up the matches.

通过使用exists，您可以有效地强制查询计划执行嵌套循环.这可能是最快的方法，但您确实希望查询计划器做出决定.

By using exists you may effectively force the query plan to do a nested loop. This may be the quickest way, but really you want the query planner to decide.

我会说您需要编写两个 SQL 语句并比较查询计划.您可能会发现它们会根据您拥有的数据而发生很大变化.

I would say that you need to write both SQL statements and compare the query plans. You may find that they change quite a bit depending on what data you have.

例如，如果 [Institutions] 和 [Results] 的大小相似，并且都聚集在 InstitutionID 上，则合并连接将是最快的.如果 [Results] 比 [Institutions] 大得多，则嵌套循环可能会更快.

For instance if [Institutions] and [Results] are similar sizes and both are clustered on InstitutionID a merge join would be quickest. If [Results] is much bigger than [Institutions] a nested loop may be quicker.

这篇关于使用 JOIN 或使用 EXISTS 可以获得更好的性能吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！