slower queries using linked server

Question

Post reply

slower queries using linked server

Snargables

SSCrazy Eights

Points: 8855
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: 246960 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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

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.