Increase performance on update query joining Linked Servers

Question

I have 2 linked servers in SQL Server, and would like to update 1 record in the remote DB.
Problem is that the query takes very long (6 minutes), and as this is only 1 record, I need to update thousands, so I need to take another approach. Any ideas or suggestions of something else that I can try?

I have indexes on LocalTable's TaskID, TaskType, and on ClosedOn+Completed+Started and on RemoteTable's TaskID, TaskType and Status.

This is what I currently have

UPDATE RemoteTable
SET RemoteTable.TaskType = LocalTable.TaskType,
    RemoteTable.Status = CASE WHEN IsNull(LocalTable.ClosedOn, 0) <> 0 THEN 'Closed'
                              WHEN IsNull(LocalTable.Completed, 0) <> 0 THEN 'Completed'
                              WHEN IsNull(LocalTable.Started, 0) <> 0 THEN 'Started'
                        ELSE 'Created'
                END,
FROM Tasks LocalTable INNER JOIN [172.1.2.3].DBName.dbo.Tasks RemoteTable
     ON Local.TaskID = RemoteTable.TaskID
where RemoteTable.TaskID = 12345

Execution plan below: The 100% for the Remote table scan is on Task ID for the Remote Table.

Out of curiosity have you tried this join `FROM Tasks LocalTable INNER JOIN [172.1.2.3].DBName.dbo.Tasks RemoteTable ON RemoteTable.TaskID = 12345 and Local.TaskID = 12345` — Conrad Frix, Mar 09 '17 at 18:26
@ConradFrix, I've tried it and stopped the process after 1 minute. Still a long time. The network connection between the servers are very good, so not so much a latency problem. — Cameron Castillo, Mar 09 '17 at 18:32
The problem here could be doing a JOIN of a local table against a remote table. That is what I imagine could be wrong. Just to try it off, have you tried joing the tables locally? Like sending the local table to the remote sever and run everything there remotely? I would recommend that you go for this: http://stackoverflow.com/questions/2209556/is-it-possible-to-create-a-temp-table-on-a-linked-server — asemprini87, Mar 09 '17 at 18:53
@jfatal, it takes 1 second. I don't have the exact milli-seconds, but not more than 1 second I would say. — Cameron Castillo, Mar 10 '17 at 06:10
@asemprini87, It's not a bad idea, I have not thought about it. In my case I don't think it will work well as the table is really big (couple of hundred thousand rows). But still worth a try. — Cameron Castillo, Mar 10 '17 at 06:12

score 1 · Accepted Answer · edited Sep 13 '19 at 09:40

I think it's slow due to the distributed transaction to the linked server.

Can you write a stored procedure on the remote server and call that stored procedure to do the update?

https://blog.sqlauthority.com/2007/10/06/sql-server-executing-remote-stored-procedure-calling-stored-procedure-on-linked-server/

If I remember correctly (old job) INSERTS don't suffer from this issue, so you could load data into a staging table on the remote server and the remote stored procedure can process those 'commands' to do the updates.

Increase performance on update query joining Linked Servers

1 Answers1