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?
