Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'll try to keep this question straightforward, though I am dealing with a big ball of mud.

When I run my test query acrossed linked servers both located locally (on a shared virtual host), the query is fast at about 9 seconds.

When I run the same query acrossed linked servers (one local, one about 1,200 miles away) it is MUCH slower at 5:23

I am trying to learn how to analyze an execution plan, but are there other probable causes for this sort of thing?

Edit: Based on @Mat's comment, here is an example.

DISCLAIMER: I do not vouch for the quality of this code.

SELECT * INTO TempQTYOnHand FROM [RemoteServer].remoteDB.dbo.QuantityOnHand 

DELETE FROM [RemoteServer].remoteDB.dbo.QuantityOnHand 

INSERT INTO [RemoteServer].remoteDB.dbo.QuantityOnHand 
SELECT DISTINCT ProductId, Onhand FROM Inventory
WHERE ProductID IN (SELECT ProductId FROM TempQTYOnHand)

DROP TABLE TempQTYOnHand 

Is this a "chatty" query, and also, I guess chatty would mean that it is round tripping for each row on the INSERT?

share|improve this question
    
Network latency could be killing your query performance. (And there's not much to do generally except rework your query/app so that it's less "chatty".) –  Mat Jun 26 '13 at 18:19
    
Execute each command one at a time. Does one in particular take longer, or are most of them slower? If it's network lag, the SELECT statements should be carrying almost all of the extra time. –  Jon of All Trades Jun 26 '13 at 18:50

2 Answers 2

On an average of 3 executions, does the below code run any faster?

SELECT ProductId INTO TempQTYOnHand FROM [RemoteServer].remoteDB.dbo.QuantityOnHand 

DELETE FROM [RemoteServer].remoteDB.dbo.QuantityOnHand 

SELECT DISTINCT ProductId, Onhand INTO NEWQuantityOnHand FROM Inventory
WHERE ProductID IN (SELECT ProductId FROM TempQTYOnHand)

INSERT INTO [RemoteServer].remoteDB.dbo.QuantityOnHand 
SELECT ProductId, Onhand FROM NEWQuantityOnHand

DROP TABLE TempQTYOnHand 
DROP TABLE NEWQuantityOnHand

That one is a small, small difference but it won't hurt to test anyway, right?

Also, there are articles that say pushing is slower than pulling, and a few say otherwise.

So, in the interest of experimenting, try both cases where you also invoke pushing from the linked server. Yeah, sadly- you'll have to create a stored procedure on the linked server to push the data, and then call it from your main query so it 'pushes' the data.

share|improve this answer
    
In case you needn't log the delete, the use of a truncate, instead of it, will also grant you a little speed up. –  user16484 Jun 26 '13 at 21:45

There are many performance issues with linked server queries. Best to avoid them all together.

But let's assume you can not.

Then, figure out where MAJORITY of the data resides: on local or remote server. Then make query run where MAJORITY of data is.

For example, if you just need few rows from Customer tbl (local serverA) and you getting data from Orders tbl from remote serverB - I would suggest copying data from Customer tbl to serverB and then just executing qry on serverB. This way query doesn't need to get data across linked server link.

share|improve this answer
    
I will give this a try and let you know. Hopefully tomorrow. –  Jim Jun 26 '13 at 18:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.