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)