Hello Borislav, the tables have ~ 45000 records in each, the problem is, one of them is a Foxpro table accessed through a linked server, the query (it's a view actually) is shown below

SELECT TOP 100 PERCENT dbo.Claim.ClaimRef,
 dbo.Claim.MasterRef,
 dbo.Claim.CustomerRef, 
dbo.Claim.CreditorName,
 dbo.Claim.PayDetailsID, 
 dbo.PaymentDetails.PayeeName,
 dbo.PaymentDetails.PayAddressID,
 dbo.Address.AddressType AS PayAddressType,
 dbo.Address.AddrLine1 AS PayAddrLine1,
 dbo.Address.AddrLine2 AS PayAddrLine2,
 dbo.Address.AddrLine3 AS PayAddrLine3,
 dbo.Address.AddrLine4 AS PayAddrLine4,
 dbo.Address.CountryCode AS PayCountryCode,
 dbo.Address.AmendDate AS PayAddrAmendDate,
 dbo.Address.AmendUser AS PayAddrAmendUser,
 dbo.PaymentDetails.ForceTT,
 dbo.PaymentDetails.TTBankName,
 dbo.PaymentDetails.TTBankAddress,
 dbo.PaymentDetails.TTSortCode,
 dbo.PaymentDetails.TTBenificiary,
 dbo.PaymentDetails.TTAccountNo,
 dbo.PaymentDetails.TTIBANNo,
 dbo.PaymentDetails.TTInstructions,
 dbo.PaymentDetails.TTAmendDate,
 dbo.PaymentDetails.TTAmendUser, 
 dbo.PaymentDetails.TTDetailsConf,
 dbo.PaymentDetails.TTDetailsConfDate,
 dbo.PaymentDetails.TTDetailsConfUser,
 dbo.PaymentDetails.AmendDate AS PayDetailsAmendDate,
 dbo.PaymentDetails.AmendUser AS PayDetailsAmendUser,
 dbo.Master.PayDetailsRequestLetterDate,
 dbo.Claim.PayDetailsConf,
 dbo.Claim.PayDetailsConfDate,
 dbo.Claim.PayDetailsConfUser,
 dbo.Claim.AmendDate,
 dbo.Claim.AmendUser,
(CASE When Exists(Select block From lcs5...multiblk  multiblk
 Where block = '94' And Multiblk.clm_ref = dbo.Claim.ClaimRef 
And Multiblk.Active = 1) then 1 else 0 end) As Deceased
FROM dbo.Master INNER JOIN dbo.Claim
ON dbo.Master.MasterRef = dbo.Claim.MasterRef 
INNER JOIN dbo.Address 
INNER JOIN dbo.PaymentDetails 
ON dbo.Address.AddressID = dbo.PaymentDetails.PayAddressID
ON dbo.Claim.PayDetailsID = dbo.PaymentDetails.PayDetailsID
ORDER BY dbo.Claim.MasterRef