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?
Best Answer
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...
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 behaviour was seen, I would love to know.