Lynn Pettis (4/7/2012)
Sounds like you are using a linked server. If you provide the query you are currently using we may be able to show you how to improve its performance. Without seeing it, there really isn't much we can do.
Actually i take it back , issue isn't around the join across servers but it is on join which happens on remote server and sends back the data. This is inside a view:
SELECT w.ID,
w.[Rdc],
w.[Pby],
w.[Tp],
w.[Mp],
w.[OpC],
w.[PType],
w.[PCde],
w.[RTye],
w.[RCde],
CASE
WHEN e.PCde IS NOT NULL THEN 'Yes'
WHEN e.pCde IS NULL THEN 'No'
END AS [Rts],
COALESCE(re.RSs, 'hold') AS RStts
FROM (select * from [remoteserver].mydb.dbo.rdw WHERE PType = 'T' ) w
LEFT JOIN [remoteserver].mydb.dbo.caur e
ON w.PCde = e.PCde
AND w.RCde = e.rCde
AND w.PTpe = e.PTpe
LEFT JOIN [remoteserver].mydb.dbo.revv re
ON w.Id = re.rDsId
So above query is executed from serverA against remoteserver and sends back the result to serverA. I think the issue is around first left join. If i ran the same query on the remoteserver runs perfectly fine. Thanks