slower queries using linked server

Question

Post reply

slower queries using linked server

Snargables

SSCrazy Eights

Points: 8852
More actions
October 6, 2016 at 12:17 pm

#314095

I'm working to optimize a rather large query who tables are all a part of the same linked server. I can’t get the exe plan or OI stats using the linked server. Therefore, I’m optimizing it in its local location. This is all fine and good. My issue is it takes much longer to run via the linked server. Is this extended time due to the data coming over the network or does the optimizer not get used when called via linked server. As I said before. I have no idea how the optimizer is handling the query because I can't get the exe plan now IO stats

Viewing 5 posts - 1 through 4 (of 4 total)

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

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 1

Have you tried EXECUTE(querystring) AT linkedservername?

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

If you are accessing the tables on the linked server and doing all the work on the local server, you are pulling all the data across the network to the local server. You have two choices here. The first is to use openquery to run the query on the remote (linked server) and only send the results of the query over the wire. The second is to create a stored procedure on the remote (linked server) and execute it from the local server and send the results over the wire.

Phil Parkin SSC Guru Points: 246716 More actions · Answer 3

Lynn Pettis (10/6/2016)
If you are accessing the tables on the linked server and doing all the work on the local server, you are pulling all the data across the network to the local server. You have two choices here. The first is to use openquery to run the query on the remote (linked server) and only send the results of the query over the wire. The second is to create a stored procedure on the remote (linked server) and execute it from the local server and send the results over the wire.

Option 2 sounds cleaner to me ... that's the direction I'd choose.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

kevaburg SSCoach Points: 18131 More actions · Answer 4

Phil Parkin (10/6/2016)
Lynn Pettis (10/6/2016)
If you are accessing the tables on the linked server and doing all the work on the local server, you are pulling all the data across the network to the local server. You have two choices here. The first is to use openquery to run the query on the remote (linked server) and only send the results of the query over the wire. The second is to create a stored procedure on the remote (linked server) and execute it from the local server and send the results over the wire.
Option 2 sounds cleaner to me ... that's the direction I'd choose.

I had exactly this problem while preparing ETL builds in a heterogenous database Environment. The problem was solved exactly as described in and confirmed by Phil in the second solution.