CODEWITHSUNDEEP
sql-servert-sqlsql-server-2005sql-server-2008-r2compatibility
Albert Laure
Albert Laure

Reputation: 1722

query that executes too long (25 mins)

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%! PIcture

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

Upvotes: 0

Views: 471

Answers (2)

Code Different
Code Different

Reputation: 93181

Well, your code was a little of a pain to read, but I'll take a stab: the JOIN statement between tblPawnMain (a) and tbl_BranchRowVersions (b) are not SARGABLE:

ON CAST(a.[PawnMainID]/1000000000000 AS decimal (38,0)) = b.fld_ID

SQL Server does not know the results of the CAST until it evaluates the function. Hence it must grab every row from tblPawnMain and tbl_BranchRowVersions. The thin arrow coming out of the Remote Query operator tell sme that the result set is small, but the JOIN may be expensive because they are huge tables and a CROSS JOIN was performed.

Try to run this from the Beautifly server directly and post your execution plan:

SELECT a.PawnMainID, b.fld_ID
FROM Beautifly.BIOS_PAWN.dbo.tblPawnMain a
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

If you you constantly joins those two tables using that CAST, it may be worthwhile to add a column on tblPawnMain that is CAST(a.[PawnMainID]/1000000000000 as decimal (38,0)) and put an index on it. Your JOIN will then be SARGEABLE.

Upvotes: 1

Sonam
Sonam

Reputation: 3466

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.

Upvotes: 3

Related Questions