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.