CODEWITHSUNDEEP
sqlsql-serverdatabase-performance
slicksnmp
slicksnmp

Reputation: 3

Slow Update Statement with Linked Server

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

Upvotes: 0

Views: 1913

Answers (2)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89256

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

Upvotes: 1

Stu
Stu

Reputation: 32619

Just something you might try is to experiment with your query to use a remote join hint.

Another alternative would be to also try the reverse, ie, running the query on the remote server with [this] server being linked.

update l set
    l.date = '9/9/2021',
    l.qty = p.pickQty
from 
tbl_pick p
inner remote join LINK.database.dbo.TBL2 l on l.recnum=p.recnum and p.CompanyCode='99' and p.Id=999

Upvotes: 1

Related Questions