Need help optimizing join that uses function call

[mwolf00]

Hi everyone,

I'm a little stuck on this one...
Table eEvent tracks actions that happen to items in rbm_management. I need to find the last event that happened before billing (the billing date is returned by the function rbm_fGetBillingDate). The code works very fast when I hard code the billing time like this.

SELECT m.dtdeadlinestart, rbm_fgetbillingdate(m.dtdeadlinestart) billDate, dt.maxtime, e.etostage
FROM RBM_MANAGEMENT m
JOIN EEVENT e ON m.efolderid = e.efolderid 
JOIN (SELECT efolderid, MAX(eeventtime) maxTime
	FROM EEVENT 
	WHERE eeventtime <= [blue]TO_DATE('7/15/2007 8PM','mm/dd/yyyy hhAM')[/blue]
	AND efromstage <> etostage
	GROUP BY efolderid) dt ON e.efolderid = dt.efolderid AND e.eeventtime = dt.maxTime
WHERE m.dtDeadlineStart BETWEEN TO_DATE('6/18/2007','MM/DD/YYYY') AND TO_DATE('9/6/2007','MM/DD/YYYY') + 1

However, when I make the billing date dynamic like this it is extremely slow

SELECT m.dtdeadlinestart, [red]rbm_fgetbillingdate(m.dtdeadlinestart) billDate[/red], dt.maxtime, e.etostage
FROM RBM_MANAGEMENT m
JOIN EEVENT e ON m.efolderid = e.efolderid 
JOIN (SELECT t1.efolderid, MAX(t1.eeventtime) maxTime
	FROM EEVENT t1
	[blue]JOIN RBM_MANAGEMENT t2 ON t1.efolderid = t2.efolderid
	WHERE t1.eeventtime <= rbm_fgetbillingdate(t2.dtdeadlinestart)[/blue]
	AND t1.efromstage <> t1.etostage
	GROUP BY t1.efolderid) dt ON e.efolderid = dt.efolderid AND e.eeventtime = dt.maxTime
WHERE m.dtDeadlineStart BETWEEN TO_DATE('6/18/2007','MM/DD/YYYY') AND TO_DATE('9/6/2007','MM/DD/YYYY') + 1

I know that this is due to the fact that the function is being fired for each row in the eEvent table. Is there a way to use the result from the function firing once in the main query?

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook