SQL 连接与 SQL 子查询(性能)? [英] SQL Joins Vs SQL Subqueries (Performance)?

问题描述

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

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 子句(WHERE x=Y OR x=Z OR...).

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 统计特别有用)并同时运行它们.确保在两次运行之间清除缓存！

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 连接与 SQL 子查询(性能)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！