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?
SQL Server – Loading Data into Local Table Before Join
linked-serversql-server-2008-r2
Related Question
- SQL Server 2014 – How to Run Built-In Functions on Linked Server
- SQL Server – Improving Query Performance Across Linked Servers
- SQL Server – Configuring Linked Server with Double Hop and Kerberos
- Sql-server – TLS 1.2 breaks SQL Server linked servers
- Sql-server – Linked Server: An error occurred during decryption
Best Answer
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?