Performance problem with linked server and resolution … (Performance tuning tips from field experiences when you are using joins in linked server)

Using linked servers in production environment is very common for many applications. Unfortunately linked servers are double edged sword available for developers. While linked servers are an excellent way to get data in real time from one server to another, poorly written linked server queries can be serious performance bottlenecks on one or both servers involved in the query or for the whole application.

When you join a local table to remote tables using linked server, one of the main problem is the local query optimizer doesn't know which records to request from the remote table. Hence it requests that the remote server transmit the entire table, and all that data is then loaded into a temporary table and then the join is done locally. This local table does not have any index unlike the original remote table. So this situation slows down the performance of linked server seriously.

To mitigate this potential problem, it is advisable to create a temporary table with proper indexing in your local server and then load the data from the remote server to local server’s temporary table. Once the temporary table has the data, then do the joins or other complex operations so that query optimizer will offer far better result.

So in reality you should create local temporary tables for each of the remote tables (which is being called by linked server) and transfer over the needed date from each of the remote tables. This strategy will improve your application and link server’s performance dramatically.

Also while linked server is a great and flexible technology, it is needed to keep in mind that it is not designed for heavy data operations. If you use linked server for extremely heavy operations (For example, I often see poorly performing linked server which is joining local table with remote table/s and iterating through logical loop possibly multiple thousands for each execution), your code’s (application’s) performance is bound to slow down. You should use more efficient technologies (like SSIS data copy technology) to first copy remote data to local server and then do necessary business operations (like join) to get major benefit in your code’s performance.