query that executes too long (25 mins)

Question

I have this query on a linked server

Select a.PawnMainID into #ExistingID 
from Beautifly.BIOS_PAWN.dbo.tblPawnMain a
inner join Transactions.tbl_PawnMain c
on a.PawnMainID = c.fld_PawnMainID
inner join Reference.tbl_BranchRowVersions b
on cast(a.[PawnMainID]/1000000000000 as decimal (38,0)) = b.fld_ID
where (a.StatusID between 3 and 5)
AND a.RowVersionBo > b.fld_MaxRowVersion

and it takes forever to finish while other codes seemed to be normal at all can you help me identify the problem? is my code note efficient enough? if so how can i improve it?

the picture below shows my execution plan having re remote query a cost of 99%!

i also want to ask if my code is optimized? if not how can i optimize it?

Answer 1

Query to remote server is always expensive, as first it will establish the connection, query the data, return the result and then close the connection. So, here to make the query faster the easiest option is, fetch the data from the remote server in a temp table and then execute the query.