SQL加入Vs SQL子查询(性能)? [英] SQL Joins Vs SQL Subqueries (Performance)?

问题描述

我想知道我是否有 join 这样的查询-

I wish to know if I have a join query something like this -

Select E.Id,E.Name from Employee E join Dept D on E.DeptId=D.Id

和子查询类似的内容-

Select E.Id,E.Name from Employee Where DeptId in (Select Id from Dept)

当我考虑性能时，两个查询中哪个查询会更快，为什么为什么?

When I consider performance which of the two queries would be faster and why ?

还有一段时间我应该优先选择另一个吗?

Also is there a time when I should prefer one over the other?

对不起，这是否太琐碎了，请问一下，但是我对此感到困惑.另外，如果你们可以建议我工具，这将是很棒的，我应该使用它来衡量两个查询的性能.非常感谢！

Sorry if this is too trivial and asked before but I am confused about it. Also, it would be great if you guys can suggest me tools i should use to measure performance of two queries. Thanks a lot!

推荐答案

我希望第一个查询更快，这主要是因为您具有等价关系和显式的JOIN.以我的经验，IN是一个非常慢的运算符，因为SQL通常将其评估为一系列由"OR"(WHERE x=Y OR x=Z OR...)分隔的WHERE子句.

I would EXPECT the first query to be quicker, mainly because you have an equivalence and an explicit JOIN. In my experience IN is a very slow operator, since SQL normally evaluates it as a series of WHERE clauses separated by "OR" (WHERE x=Y OR x=Z OR...).

尽管与ALL THINGS SQL一样，您的里程可能会有所不同.速度很大程度上取决于索引(您是否在两个ID列上都有索引?这将有很大帮助...).

As with ALL THINGS SQL though, your mileage may vary. The speed will depend a lot on indexes (do you have indexes on both ID columns? That will help a lot...) among other things.

唯一可以确定100％确定性的最快方法是打开性能跟踪(IO Statistics特别有用)并同时运行它们.确保在两次运行之间清除缓存！

The only REAL way to tell with 100% certainty which is faster is to turn on performance tracking (IO Statistics is especially useful) and run them both. Make sure to clear your cache between runs!

这篇关于SQL加入Vs SQL子查询(性能)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！