SQL UDF performance (was: SQL - Calculate the number of hours between 2 timestamps)

Birgitta,

Here's the statement: http://code.midrange.com/index.php?id=2b3d23e160  

The statement is indeed in a SQLRPGLE and the programmer did end up
using numeric host variables to compare to (like I suggested to the
programmer who wrote this statement, eventually) but there is a join to
a table that contains real date fields to match to numeric date fields.
Nothing can be done about the different data types for the date fields,
yet.  By the way, the statement results are inserted into a work file to
be used by other programs.    

SQL statement back ground. We needed to extract vendor rebate totals and
item sales totals information summed for a rebate period.   The rebate
or item sales totals can be null to indicate none for that period.  

I in no way am condemning your posted UDFs or the use of them; I was
just sharing my first real time experience of UDFs. I have often seen
UDFs in articles and discussions and thought that they would have
potential for our shop.  You post for the date SQL UDF meet the
requirement that lit my fuse and I pushed to have it accepted in a
statement I thought it would fit.  I was completely disappointed by the
performance.   

The performance using the SQL UDF was, to put it mildly, appalling.  So
bad in fact the first test using the SQL UDF we let run for over an hour
before killing it.  Before and after that, not using the SQL UDF it
would run in about 1 minute.

I created some tests using SQL and RPGLE UDFs and some more using
straight data conversion syntax.  Overwhelmingly, the tests using the
data conversions within the SQL statement performed better.   These
statements are very simple test.  The UDFs were using only in the
results set like,  SELECT Fld1, YYMDToDate(NumDate) FROM File.  Running
it over 1,000,000+ records I never let the SQL UDFs finish (after
waiting for 10 minutes verse 1 or 2 I did not see the point in letting
it finish). 

Thanks, Matt