December 4, 2014 at 2:15 pm
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.
December 5, 2014 at 9:51 am
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.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 5, 2014 at 10:05 am
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