October 14, 2017 at 8:20 am
Hello.
Looking at an SP that used to run in a couple of seconds and does something like this :
Select X,Y,U as Z into #TMP from TableA as A Inner Join Table B............
UNION ALL
Select X,Y,V as Z into from TableA as A Inner Join Table B............
UNION ALL
Select X,Y,W as Z from TableA, as A Inner Join Table B..........
Select X,Y,Z from #TMP.
All was good until 3 additional subqueries (?) were added pulling the additional result sets from a linked server using 4 part named joins.
I understand what the problem is with this approach but am struggling to incorporate a working solution into this SP.
For Instance, in the past I have amended a bunch of these SPs by putting the SP on the linked server and executing it over there using EXEC.
I can also execute each individual query on the linked server and just pull back the result set using Exec('') at[], once I figured out the ESCAPEs
for all of the quotes.
Where I am struggling is with trying to incorporate the results from the EXEC('') at [] on linked server with the UNION ALLs on the Local Server
into this SP rather than executing the whole SP remotely.
I think that I need to do something different with #TMP but not sure what.?
I am also puzzled as to why only the first Select sub-query specifically mentions into #TMP and the others don't - is this something to do with UNION ALL?
Now I look at the code I am questioning if it can be rewritten as 2 subqueries rather than 6 (which I will look at now) but I will still be left with the same issue of combining the output form the local and linked server queries.
Regards
Steve O.
October 14, 2017 at 8:55 am
Actually I think that I can get openquery to work - not sure why I couldn't do this earlier but still playing with it
Steve O.
October 19, 2017 at 6:21 am
OPENQUERY or OPENROWSET can at least limit the amount of data that has to traverse the network to your local server from the linked server. The important thing to recognize is that when you run a query on the local server, and it joins to a Linked Server table, then that entire table has to come across the network in order for the local server to perform the join. Not generally a good idea for large tables.
If you're doing a UNION ALL just to get different columns in the linked server table, you should probably get ALL of those columns using OPENQUERY and slap that data into a temp table, and then post back and we can help you formulate a more efficient solution from there. You'll just need to post table create statements and provide insert statements with sample data, along with the expected output given said sample data, and folks will gladly help out.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 28, 2017 at 6:45 am
Thanks for the reply Steve.
I did get it working using OpenQuery, and my intent was to specifically to reduce the network overhead as you say.
I had fun and games escaping all of the single quotes (usually column names) but got there in the end,
I also had the network folks pull the traffic over the NICs while the query was running (with and without Openquery),
and captured the execution plans so I can meet with our Developers next week to demonstrate what I have been saying.
The effect was dramatic for the first query I looked at because it went from @8 millions rows returned to 3 rows (i.e. the result set only and not the data).
Oddly the execution plan for the remaining two that I looked did not indicate that it was doing the same thing as the first query, but I changed these also.
If I get the chance to look a bit closer I will post back - but we may have migrated the date by then.............
Steve O.
October 30, 2017 at 9:08 am
Glad you got it working. Getting just 3 rows to cross the network instead of 8 million is a pretty amazing demonstration of why Linked Server JOINs can be a nightmare. Developers definitely need to understand the impact of trying to use a Linked Server as if it was just part of an object name.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply