1

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?

1
  • 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
    Commented Jul 28, 2014 at 23:41

2 Answers 2

2

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?

2
  • How's every 3 months? Is that frequent?
    – leeand00
    Commented Jul 29, 2014 at 0:34
  • For me, frequent would be a synch running every hour or less.
    – SandraV
    Commented Jul 29, 2014 at 15:31
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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