INNER JOIN on Linked Server Table is much slower than Sub-Query

Question

I came across this very strange situation, and I thought that I would throw it into the crowd to find out WHY.

I have a query that joined 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 spoke forever (didn’t even find the actual runtime), and it was then that I noticed that it Bhad no index, so I added it, but this does not fix the problem. 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 request, in my opinion, should at least have the same results, but here is its answer immediately!

Any ideas why one could hang and the other fast? And yes, I waited to make sure the index was created before both were launched.

Answer 1

, ( ) , sql server, , . . , - : INNER MERGE JOIN.

:

http://msdn.microsoft.com/en-us/library/ms181714.aspx

Answer 2

2- , 1- inner loop join roundtrip A

Answer 3

? , , , ?

Answer 4

. remote.b, ?

If so, the reason the second request is faster is because you make one request to another server and get all the fields you need, from b, before processing the data. In the first request, you process the data, and at the same time, you make several requests to another server.

Hope this helps you.