Update over linked servers slow

Question

Update over linked servers slow

Siqing LU

Say Hey Kid

Points: 665
More actions
January 11, 2010 at 1:45 pm

#137200

HI,
I need to update a tabe with joing to remote server. If I run the query from the source server, the performance is extremely slow and almost never finish. I traced the update and found the updating uses the cursor to update each row in the target, but it run fast if I run the query in the target server (where the updated table located), it finish in a few minutes and profiler shown it does not use cursor.
update serverA.dbname.dbo.tablename
set name =a.name
from serverB.dbname.dbo.tablename a inner join serverA.dbname.dbo.tablename b
on a.key = b.key
If I run it in serverB, it is fast, but If I run it in serverA, if it is very slow. I tried different servers, remote join, etc, all same.
Is it the normal behavior of linked server or I did something incorrect?
Thanks

Viewing 14 posts - 1 through 13 (of 13 total)

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

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 1

If you are running queries that join between tables in the two server instances, and transferring large amounts of data, then you have a bottleneck that you need to be aware of.

If the servers are on their own subnet with a 1GB link, then you should not have to worry a great deal. I would be concerned if the two servers are connected by a shared, slow link.

HTH...

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

Siqing LU Say Hey Kid Points: 665 More actions · Answer 2

Both servers are in same subnet with a 1GB link and both servers are SQL 2005 64 bit in windows 2003 server 64 bit.

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 3

Try a look at this KB article here. May be you can relate it to your problem

Thanks..

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

Siqing LU Say Hey Kid Points: 665 More actions · Answer 4

Siqing LU

Say Hey Kid

Points: 665

January 11, 2010 at 2:14 pm

#1102052

Thanks, will try.

andrewkane17 SSCertifiable Points: 7529 More actions · Answer 5

What rights does the login have that has been used to set up the linked server, if it has limited rights it might not be able to read statistics and may not be choosing the best plan, just a guess.

http://sqlblog.com/blogs/linchi_shea/archive/2009/07/21/performance-impact-linked-server-security-configuration-and-how-it-can-hurt-you.aspx

Andrew

Siqing LU Say Hey Kid Points: 665 More actions · Answer 6

Thanks, The linked server uses sa, so it should have enough right.

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 7

Siqing LU, did the artice THE_SQL_DBA suggested helped you? I was reading it and it seems to be the same problem you have, so I thing adding a unique constraint or a primary key constraint must help you to solve the problem.

My blog

Siqing LU Say Hey Kid Points: 665 More actions · Answer 8

I do have unique constraint in the tables, but not help.

Thanks

chloe.williams SSC Enthusiast Points: 123 More actions · Answer 9

I had a similar problem, although back in SQL Server 2000. If you can't find the root cause of your problem, a bit of pragmatism may be in order and use an alternative solution that does work.

I created a DTS package on both Server A and Server B. Server A output data to be updated to Server B and then executed the package on Server B. Package on Server B updated the data and returned to a temp table on Server A. Server A then continued it's package and used a correlated sub query to perform the relevant updates on the original table. I KNOW this may not be the most elegant solution but it works a hundred times faster for me than joining tables on linked servers did!

That's not to say that having seen some of the other solutions here I may not try some of them as and when I get the time...;-)

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 10

I would suggest following Chloe's suggestion, but not necessarily with DTS/SSIS. I suspect that restructuring your query to get all of ServerB info first, then perform the join may perform better. Something like:

update serverA.dbname.dbo.tablename

set name =a.name

from serverA.dbname.dbo.tablename b inner join

( select key, name from serverB.dbname.dbo.tablename) as a

on a.key = b.key

Hope that helps

Mark

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

Using 2005, pulling relevant rows from the linked server into a #temp table can dramatically improve performance.

SELECT key, name

INTO #Remote

FROM serverA.dbname.dbo.tablename

UPDATE FROM with a JOIN can be unpredictable if you dick around with the placement of source tables. Check if these two rules are followed: -

1. The source table containing the target column(s) is the table referenced as FROM, not the INNER JOINed table.

2. The table alias is used to reference the target table, i.e. not the table name.

Your update should look like this:

UPDATE b

SET [name] = a.[name]

FROM dbname.dbo.tablename b -- <--- serverB

INNER JOIN #Remote a

ON a.key = b.key

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Siqing LU Say Hey Kid Points: 665 More actions · Answer 12

Thanks, Yes, I did same thing. I was wondering what is the reason why the linked server behavior like that.

ryanm-829779 Valued Member Points: 67 More actions · Answer 13

Thanks a ton Chris M! Your response was very helpful for me as well 😉