First we get the max id from the ProductFileLocalName and then - 1000 (as we don't want to delete the most recent additions as they might not be inserted in ProductFileInfo yet)
Then we pass the max id to this stored procedure:
DELETE TOP (10000)
FROM ProductFileLocalName WITH (ROWLOCK)
FROM ProductFileLocalName
LEFT OUTER JOIN ProductFileInfo AS pfi WITH (NOLOCK) ON ProductFileLocalName.ProductFileLocalNameId = pfi.ProductFileLocalNameId
WHERE (ProductFileLocalName.ProductFileLocalNameId < @maxid AND pfi.ProductFileInfoId IS NULL);
Is this the most effective way to perform this operation?