Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I was just reading the following article which states that it's a worst practice to do JOINs between two linked servers. The only alternative I can see is to get the necessary data from both linked servers and load them into temporary tables in an SQL Server instance and then do the join. Is this advisable? Or is there some other course of action?

share|improve this question
    
What does such a problematic query plan look like? Shouldn't SQL Server pull both sources of the join automatically? I don't see what a temp table would help. –  usr Jul 28 at 23:41

2 Answers 2

up vote 2 down vote accepted

I've used this technique in data synch jobs. It helps if you don't need the entire table from the remote server (just a few columns or you can filter it down in your WHERE clause). And, by bringing the data into a temp table on the instance where you're doing the most work, you can index the temp table to your needs and get statistics that are helpful to the work you're doing.

Of course, if you're trying to do this on a very frequent basis, it might not be the best solution for you. Maybe SSIS?

share|improve this answer
    
How's every 3 months? Is that frequent? –  leeand00 Jul 29 at 0:34
    
For me, frequent would be a synch running every hour or less. –  SandraV Jul 29 at 15:31

Yepyep, bring the data from two servers to one server, then do your join there. When I set this up, the business were adamant it was a once-a-month report, turns out it was weekly... So I setup a SQL job that moved data from one server to the other, into a staging database. Job would truncate the staging table, then be a SELECT INTO grabbing only the data it needed.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.