SQL Server update query on linked server causing remote scan

I have a SQL Server 2012 setup as a linked server on a SQL Server 2008 server.

The following queries executes in less than 1 second:

SELECT kg.IdGarment
   FROM Products p 
   INNER JOIN ZAMStaffRentals r ON r.ProductID = p.UniqueID 
   INNER JOIN ZAMCustomer_Staff s ON r.StaffID = s.UniqueID 
   INNER JOIN ZAMRentalGarments g ON g.StaffRentalID = r.UniqueID 
   INNER JOIN [REMOTE_SERVER].[REMOTE_DB].dbo.t_ProcessIT_Garment kg on g.BarcodeRFID = kg.IdGarment 
   INNER JOIN ZAMKannChanges log on log.GarmentID = g.UniqueID 
   WHERE log.ActionType = 'I'

t_ProcessIT_Garment contains 37,000 rows, the query returns two records, the IdGarment column is the Primary Key. No problem here.

However, if I run this query to do a remote update, it takes 24 seconds, and 2 rows is affected:

UPDATE [REMOTE_SERVER].[REMOTE_DB].dbo.t_ProcessIT_Garment SET 
   IdGarment = IdGarment
   FROM Products p 
   INNER JOIN ZAMStaffRentals r ON r.ProductID = p.UniqueID 
   INNER JOIN ZAMCustomer_Staff s ON r.StaffID = s.UniqueID 
   INNER JOIN ZAMRentalGarments g ON g.StaffRentalID = r.UniqueID 
   INNER JOIN [REMOTE_SERVER].[REMOTE_DB].dbo.t_ProcessIT_Garment kg on g.BarcodeRFID = kg.IdGarment 
   INNER JOIN ZAMKannChanges log on log.GarmentID = g.UniqueID 
   WHERE log.ActionType = 'I' ;

I tested using IdGarment = IdGarment to keep things simple. The execution plan shows it uses Remote Query for the first query, but Remote Scan for the second query, which has 100% of the cost.

The table joins are identical in both queries, why is it using Remote Scan for the second query, and how do I fix this?

A friend just had a similar problem and rewriting the query solved it.

I know this is an old question, and likely you have worked it out, but for anyone else you could try this and report back...

UPDATE kg
SET 
   IdGarment = IdGarment
   FROM Products p 
   INNER JOIN ZAMStaffRentals r ON r.ProductID = p.UniqueID 
   INNER JOIN ZAMCustomer_Staff s ON r.StaffID = s.UniqueID 
   INNER JOIN ZAMRentalGarments g ON g.StaffRentalID = r.UniqueID 
   INNER JOIN [REMOTE_SERVER].[REMOTE_DB].dbo.t_ProcessIT_Garment kg on g.BarcodeRFID = kg.IdGarment 
   INNER JOIN ZAMKannChanges log on log.GarmentID = g.UniqueID 
   WHERE log.ActionType = 'I' ;

I don't know why this helped, but using the UPDATE "alias" form of the statement meant that the whole UPDATE query was remoted, rather than pulling the data back to the local instance to perform the JOINs.

If anyone has any ideas why this behavior was seen, I would love to know.

Microsoft Docs has a good article about the subject (even if it's a bit old).

StackExchange Database Administrators Q#36893, answer score: 2