Linked Server Update "Hung"

Question

Linked Server Update "Hung"

homebrew01

SSC Guru

Points: 55549
More actions
July 29, 2016 at 12:00 pm

#328750

I am doing a pretty simple update of Table_A, based on a join to Table_B
The query runs on Server_A, via Linked Server to Server_B, where both tables reside.
If I run the query directly on Server_B in a query window, it takes about 1 second, and the execution plan looks good, doing an index seek on the large table being updated (but the # of records updated is small due to the Table_B being small)
If I run a select & join on Server_A to Server_B, via the Linked server, selecting the same records that will be updated, it takes 2 seconds.
But running the update on Server_A to Server_B, via the Linked server, was running 10 minutes and still not completed.
So, what quirk would cause the update to run slowly across the Linked server ?

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

homebrew01 (7/29/2016)
I am doing a pretty simple update of Table_A, based on a join to Table_B
The query runs on Server_A, via Linked Server to Server_B, where both tables reside.
If I run the query directly on Server_B in a query window, it takes about 1 second, and the execution plan looks good, doing an index seek on the large table being updated (but the # of records updated is small due to the Table_B being small)
If I run a select & join on Server_A to Server_B, via the Linked server, selecting the same records that will be updated, it takes 2 seconds.
But running the update on Server_A to Server_B, via the Linked server, was running 10 minutes and still not completed.
So, what quirk would cause the update to run slowly across the Linked server ?

depends on your update;

are you doing

update MyTarget F

ROM LinkedServer.db.dbo.tablename MyTarget

SET ...?

remember that format copies the entire table locally, then does the update in tempdb, then pushes the whole table back.

big table, or updating of PK's might hang or take a looong time.

if you REALLY need to do an update, you usually want to use the EXEC(@cmd) AT LinkedServerName or UPDATE OPENQUERY(LinkedServerName,'UPDATE CommandAsHardcodedString')

EXECUTE ( 'CREATE TABLE Sandbox.dbo.SalesTable

(SalesID int, SalesName varchar(20)) ; ' ) AT PROD2K8;

SET @sql = 'UPDATE OPENQUERY(LINK_ORA,''SELECTLOCNO, COUNTY, LSTCHGDT, LSTCHGOPER

FROMTheTable

WHEREGRPNO = ' + CONVERT(varchar(5),@GRPNO) + '

AND EMPNO = ' + CONVERT(varchar(5),@EMPNO) + '

AND DEPNO = ' + CONVERT(varchar(2),@DEPNO) + '

AND (COALESCE(LOCNO,-99) != ' + CONVERT(varchar,@LOCNO) + '

OR COALESCE(COUNTY,'''''''') != ''''' + CONVERT(varchar,@County) + ''''')

'')

SET LOCNO = ' + CONVERT(varchar,@LOCNO) + ',

COUNTY = ''' + @County + ''',

LSTCHGDT = ''' + @CURDATE + ''',

LSTCHGOPER = ' + @OPER

EXEC(@SQL)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

homebrew01 SSC Guru Points: 55549 More actions · Answer 2

Pulling the whole table back must be the problem. Thanks.

I think I knew that at one point 😉

I was trying this:

update LinkedServer.MyDatabase.dbo.Profiles

set Email1 = 'XYZ'

from LinkedServer.MyDatabase.dbo.Profiles P

JOIN LinkedServer.MyDatabase.dbo.Complaints C on C.ID = P.ID

where Email1 not like 'XYZ'

AND C.DateBlocked IS NULL

Lowell SSC Guru Points: 323518 More actions · Answer 3

i think it would be like this, right?

DECLARE @TermToTweak VARCHAR(30) = 'lowell@fake.com'

DECLARE @cmd VARCHAR(MAX) = 'EXECUTE AT(LinkedServer,''update P

set P.Email1 = ''' + @TermToTweak + '''

from MyDatabase.dbo.Profiles P

JOIN MyDatabase.dbo.Complaints C on C.ID = P.ID

where P.Email1 not like ''' + @TermToTweak + '''

AND C.DateBlocked IS NULL'') AT LinkedServer'

EXECUTE(@cmd)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

homebrew01 SSC Guru Points: 55549 More actions · Answer 4

Thanks. I think I will use this variant which worked:

EXECUTE ('update P set P.Email1 = Email1 + ''XYZ''

from MyDatabase.dbo.Profiles P

JOIN MyDatabase.dbo.Complaints EC on EC.ID = P.ID

where P.Email1 not like ''XYZ'' AND EC.DateBlocked IS NULL') AT [LinkedServer]