Linked Server Query Logic

  • Hello,

    I'm reviewing a stored procedure that contains tons of linked server queries so I need some help.

    There are two servers, Server A and Server B. Server A is running the stored proc. This proc is running selects against Server B dozens of times, many times for the same table. I would like to change this logic since it looks to be very inefficient. Since there are only about 8 tables, all of which are referenced in the query at least 3 times each, I think it would be much better to bring those tables over first. I could do temporary tables, but what's the thoughts behind running an SSIS copy to bring the data over from Server B to A, and then running the selects on Server A.

  • It depends on the the needs of the business. Does the query need real-time access to the data on the linked server? For real time queries, I would use a temp table in the stored procedure. You could also consider transactional replication for this scneario.

    If there is not a need for real-time data, I'd definitely look at SSIS to keep the data locally.

  • You are correct that remotely joined tables are very inefficient and the source for many types of problems. If you have a SQL query that joins local tables with remote tables, then you can perhaps first select needed remote data into a local temp table. If the SQL query is joining only remote tables together to get a local resultset, then I'd reccomend executing a "pass-through" query using something like the following.

    EXEC ('select ...') AT [LINKED-SERVER];

    When it comes to performance optimization, this is a ripe and low hanging fruit. I've reduced ETL processes that previously took hours down to a couple of minutes by refactoring T-SQL stored procedures to use pass-through queries instead of 4-part named remote join syntax.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply