Updating linked server with temp table join

Question

I am performing the following operation:

Update A set A.col1 = B.col1
From LinkedServer.LinkedDatabase.dbo.remoteTable A
JOIN #localTempTable B on B.column = A.primaryKeyColumn

The performance is terrible and the query takes what seems to be forever, given that i can't just create a table to store the temp table data in, what can I do in SQL Server 2014 to improve this? I am considering running the query from the linked server instead.

Answer 1

current query

Update linked 
 set linked.col1 = local.col1
 From LinkedServer.LinkedDatabase.dbo.remoteTable linked
 JOIN #localTempTable local 
 on local.column = linked.primaryKeyColumn 

It is typically better to perform the write operation locally
Here you are both reading from and writing to the linked server

My recommendation is if you are going to create #localTempTable then do so on LinkedServer. Have a session on the LinkedServer and link to local to populate #localTempTable but #localTempTable is now on LinkedServer. Flop the link.

insert into #linkedLocalTempTable 
select ... 
from Local.LinkedDatabase...

Please post the query to populate #localTempTable
If you use that table for other stuff then flopping stuff around may not be and option