CODEWITHSUNDEEP
sql-serversql-server-2005performancet-sqluser-defined-functions
Dustin Laine
Dustin Laine

Reputation: 38533

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.

Upvotes: 1

Views: 637

Answers (4)

gbn
gbn

Reputation: 432411

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

Upvotes: 1

egrunin
egrunin

Reputation: 25073

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)

Upvotes: 0

Zack Angelo
Zack Angelo

Reputation: 423

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)?

Upvotes: 2

HLGEM
HLGEM

Reputation: 96600

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.

Upvotes: 0

Related Questions