链接服务器表上的 INNER JOIN 比子查询慢得多
我遇到了这种非常奇怪的情况，我想我应该把它扔给人群来找出原因。
我有一个连接链接服务器上的表的查询：
select a.*, b.phone
from table_a a,
join remote.table_b b on b.id = a.id
(lots of data on A, but very few on B)
这个查询一直在说话（甚至从未发现实际的运行时间），就在那时我注意到 B 没有索引，所以我添加了它，但这并没有解决问题。最后，出于绝望，我尝试了：
select a.*, b.phone
from table_a a,
join (select id, phone from remote.B) as b on b.id = a.id
这个版本的查询，至少在我看来，应该有相同的结果，但是你瞧，它立即响应！
有什么想法为什么一个会挂起而另一个会快速处理吗？是的，我确实在运行两者之前等待确保索引已构建。
I came across this very odd situation, and i thought i would throw it up to the crowd to find out the WHY.
I have a query that was joining a table on a linked server:
select a.*, b.phone
from table_a a,
join remote.table_b b on b.id = a.id
(lots of data on A, but very few on B)
this query was talking forever (never even found out the actual run time), and that is when I noticed B had no index, so I added it, but that didn't fix the issue. Finally, out of desperation I tried:
select a.*, b.phone
from table_a a,
join (select id, phone from remote.B) as b on b.id = a.id
This version of the query, in my mind as least, should have the same results, but lo and behold, its responding immediately!
Any ideas why one would hang and the other process quickly? And yes, I did wait to make sure the index had been built before running both.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
这是因为有时（很多时候）由sql server引擎自动生成的执行计划并不像我们想要的那么好和明显。您可以查看这两种情况下的执行计划。我建议在第一个查询中使用提示，例如：INNER MERGE JOIN。
以下是有关此内容的更多信息：
http://msdn.microsoft.com/en -us/library/ms181714.aspx
It's because sometimes(very often) execution plans automatically generated by sql server engine are not as good and obvious as we want to. You can look at execution plan in both situations. I suggest use hint in first query, something like that: INNER MERGE JOIN.
Here is some more information about that:
http://msdn.microsoft.com/en-us/library/ms181714.aspx
对于链接服务器，第二个变体在本地预取所有数据并执行连接，因为第一个变体可能会为 A 中的每一行执行到链接服务器的
内循环连接往返For linked servers 2nd variant prefetches all the data locally and do the join, since 1st variant may do
inner loop joinroundtrip to linked server for every row in A远程表不在该服务器上？是否有可能连接实际上对远程表进行了多次调用，而子查询对表数据的副本发出了单个请求，从而减少了网络等待时间？
Remote table as in not on that server? Is it possible that the join is actually making multiple calls out to the remote table while the subquery is making a single request for a copy of the table data, thus resulting in less time waiting on network?
我在这里只是猜测一下。当您访问remote.b时，它是另一台服务器上的表吗？
如果是，第二个查询更快的原因是，在处理数据之前，您对另一台服务器执行一次查询并从 b 获取所需的所有字段。在第一个查询中，您正在处理数据，同时向其他服务器发出多个请求。
希望这对您有帮助。
I'm just going to have a guess here. When you access remote.b is it a table on another server?
If it is, the reason the second query is faster is because, you do one query to the other server and get all the fields you need from b, before processing the data. In the first query you are processing data and at the same time you are making several requests to the other server.
Hope this help you.