Slow Update Statement with Linked Server

Question

The linked server is to a database on the same instance. The reason the linked server is there is because the database is on another availability group on the instance.

If the availability groups are on the same node, the behavior is the same, and the update runs for over 30 seconds.

UPDATE LINK.database.dbo.TBL2

Answer 1

As @GarethD correctly states, joining across linked servers is a pit of despair.

The safe path is to always be explicit about how you push data around between Linked Servers. You can efficiently pull from a linked server with SELECT, and you can push bulk data to a linked server with JSON or XML. So I would try something like this (here everything is in TempDb accessed over a loopback linked server but you get the idea):

use tempdb
drop table if exists TBL_Pick 
drop table if exists TBL2
go
create table TBL2(RECNUM INT,[DATE] date, QTY int)
insert into TBL2(RECNUM, [DATE],QTY) values (1,'9/9/2021',0)
insert into TBL2(RECNUM, [DATE],QTY) values (2,'9/9/2021',0)
insert into TBL2(RECNUM, [DATE],QTY) values (3,'9/9/2021',0)
insert into TBL2(RECNUM, [DATE],QTY) values (4,'9/9/2021',0)

create table TBL_Pick(CompanyCode varchar(200), ID int, recnum int, PickQty int)
insert into TBL_Pick(CompanyCode,ID,recnum,PickQty) values ('99',999,1,2)
insert into TBL_Pick(CompanyCode,ID,recnum,PickQty) values ('99',999,2,3)
insert into TBL_Pick(CompanyCode,ID,recnum,PickQty) values ('99',999,3,5)
go


declare @pick nvarchar(max) = 
(
 SELECT RECNUM,PickQty 
 FROM TBL_Pick 
 WHERE CompanyCode = '99' AND ID = 999 
 for json path
)

select *
from openjson(@pick)
       with 
       (
         RECNUM int, 
         PickQty int
       ) p

exec LOOPBACK.tempdb.sys.sp_executesql N'
with q as
(
  select t.Date, t.Qty, p.PickQty 
  from TBL2 t
  join openjson(@pick)
       with 
       (
         RECNUM int, 
         PickQty int
       ) p
     on p.RECNUM = t.RECNUM
  where t.DATE = ''9/9/2021''
)
UPDATE q
SET Qty = PickQty',
    N'@pick nvarchar(max)',
    @pick = @pick;
go

select * from tbl2