Remote Join Hint 2

[Skittle]

Stumbled across the concept of a remote join hint today but it is not well explained and I havn't found a site that does explain it well.

When would you want to specify a Remote Join hint on a non local table and what benefit do you receive?

SELECT * FROM
MyLocalTable A
INNER REMOTE JOIN
MyRemoteServer..MyRemoteTable B
ON A.Col1 = B.Col1


Dazed and confused
(N+, MCAD)

 

[Catadmin]

Don't know if you saw this on MS's website or not, but I thought I'd copy it into this post.

REMOTE

Specifies that the join operation is performed on the site of the right table. This is useful when the left table is a local table and the right table is a remote table. REMOTE should be used only when the left table has fewer rows than the right table.

If the right table is local, the join is performed locally. If both tables are remote but from different data sources, REMOTE causes the join to be performed on the right table's site. If both tables are remote tables from the same data source, REMOTE is not necessary.

REMOTE cannot be used when one of the values being compared in the join predicate is cast to a different collation using the COLLATE clause.

REMOTE can be used only for INNER JOIN operations.

Looks to me that the only times you would use it is A) when you are joining a local table to a table on a remote server and B) the table on the remote server has more records than your local table.

Don't be fooled. Even though it sounds like it's doing a left outer join via the keywords INNER REMOTE JOIN, what it is really doing is determining which Server gets the bulk of the workload. Essentially, MS is saying put the remote table on the right of your join, use the keyword and it will send the query off to that machine for processing while saving the local resources for other matters.

I could be wrong in my interpretation, but that's how I'm reading it. Any other thoughts or comments on this would be greatly appreciated. @=)



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[Skittle]

Thanks for the posting. This a real mind bender of an issue.

I had spotted this on MSDN but didn't fell I understood it.
It's one of those situations where you have to accept the description even if you don't understand why - if you know what I mean.

I see that you have to take condition a) and b) together to make it sound sensible. I think I was looking at them as independent conditions which was confusing me in a big way.

Therefore....( my brain hurts ) you are suggesting
the join type is not in any way influenced by the REMOTE
and the join work will actually take place on the remote server and yield the same results but with a different timescale - dependent on the comparative server power etc.

This would explain why the table on the remote server should have more rows ( that really confused me before ). But if the processing were done on the remote server it would make sense you want to import as few rows as possible from the local server.

Soooooo in essense the 'REMOTE' hint on a join makes your local server the remote server from the queries point of view. The query is passed to the remote server which does the work and pulls anything it needs from our local server!!

It sounds a good explanation.

)





Dazed and confused
(N+, MCAD)

 

[zhavic]

This is how I understand it:

Let say you has local_table with 10 rows and remote_table with 1 milion rows.

So if you use

SELECT * FROM
local_table A
INNER REMOTE JOIN
MyRemoteServer..remote_table B
ON A.Col1 = B.Col1

than values of local_table.Col1 for 10 rows are sent to remote server, remote server will create result for this join and send it back with all other needed values from remote_table for the rows of that result

If you don't use REMOTE hint option, than
values of remote_table.Col1 for 1 milion rows are sent to local server, and local server will create result for this join, than it brings all other needed values from remote_table for rows of
that result, so there will be more network traffic

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[Catadmin]

That's pretty much how I read it too, Zhavic.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[Skittle]

Thanks fellas.

That has really helped me out.




Dazed and confused
(N+, MCAD)