cross server joins - microsoft sql server

[dbmsguy]

I am currently in a situation where we are required to do cross server joins for several warehouse/mining type queries..... the joins are killing us performance and server wise.... i am looking to find someone/doc or whatever that can give more info on how cross server joins are managed internally, especially from the optimizer perspective. i would love to speak with anyone with experience in tuning this bad boys.

thanks,
Paul

http://www.dbmsguy.com

:-V

 

[foxdev]

I've had to do this several times, where I've had a data mart on a local, high-powered machine, and monster SAP database on a server 600 miles away. When executing a query that uses the distant one as a linked server, it often took forever, even on tables of just a few thousand rows.

I found that if I first queried the linked server for the desired subset and put that in a #temp table, then executed the join from the local table(s) against the #temp table, it worked much, much faster (as in 10 seconds vs. 10 minutes).

But - my situation may have at least something to do with relatively slow network connections to the remote box. Robert Bradley
Sr. DBA, some big company
cheap prints and oil paintings:

http://www.lowcostprints.com