SQL Server Multiple Joins Are Taxing The CPU

I have a stored procedure on SQL Server 2005. It is pulling from a Table function, and has two joins. When the query is run using a load test it kills the CPU 100% across all 16 cores! I have determined that removing one of the joins makes the query run fine, but both taxes the CPU.

 Select 
  SKey
 From 
  dbo.tfnGetLatest(@ID) a 
  left join [STAGING].dbo.RefSrvc b on 
   a.LID = b.ESIID
  left join [STAGING].dbo.RefSrvc c on 
   a.EID = c.ESIID 

Any help is appreciated, note the join is 开发者_开发知识库happening on the same table in a different database on the same server.

This may shed some light on the problem. Can you separate the first join out into a CTE?

Something like this:

with FirstJoin(SKey,EID) as (
   select a.Skey,a.EID from 
       dbo.tfnGetLatest(@ID) a 
       left join [STAGING].dbo.RefSrvc b on a.LID = b.ESIID
)


select Skey from FirstJoin fj 
    left join [STAGING].dbo.RefSrvc c on fj.EID = c.ESIID

Also, since both of your joins are left joins how do they narrow the result set? Isn't that query equivalent to select sKey from dbo.tfnGetLatest(@ID)?

What does dbo.tfnGetLatest(@ID) return and is it inline table valued or multi-statement?

If it's multi statement then

• it's a black box to the optimier
• cardinality is one
• there are no statistics for the results of the udf

Please see my answer here for why udfs can be bad

So what is the differnce in your execution plan? Is this even using an index?

Have you tried using a UNION all instead (I preseume what you are trying to get is the records which have one or the other id which your current query wouldn;t give, all ti woudl give is a complete list of all the skey values, don't need to join at all for that.)

Select  
    SKey 
From  
    dbo.tfnGetLatest(@ID) a  
    left join [STAGING].dbo.RefSrvc b on  
        a.LID = b.ESIID 
union all
Select  
    SKey 
From  
    dbo.tfnGetLatest(@ID) d  
    left join [STAGING].dbo.RefSrvc c on  
        d.EID = c.ESIID  

Still might not be efficient if it isn't using an index but it would return a better recordset probably.

Or consider puttingthe values returned from the tablevalued function into a temp table where it can be indexed and then doing the join.

This would be a lot clearer with a little sample output, but I'll jump in. What does this do for you?

SELECT SKey FROM dbo.tfnGetLatest(@ID)
WHERE LID IN
    (SELECT ESIID from [STAGING].dbo.RefSrvc)
AND EID IN
    (SELECT ESIID FROM [STAGING].dbo.RefSrvc)