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?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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?
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.