RE: AW: SQL - Calculate the number of hours between 2 timestamps

Charles,

I must admit I am some what ignorant on UDFs in general.  I have never
written one before, but have always wanted to get them in to my skill
set. So, I was actually quite excited to start using UDFS when I read
Birgitta's post.  I showed it to my manager and he liked the idea of
using it, because we had a SQL statement that the UDF Birgitta posted
fit perfecting into.    I created exactly as shown except for the
library and name.  

The SQL statement would take originally a couple of minutes to process
and we never truly waited for the statement to finish when we added the
UDF.  In one case it was running over an hour before we decided to
cancel to revert back.  

A test I did over approximately 1,000,000 records with a date in numeric
*ISO format (8.0p) using the raw code from the UDF took about 2 minutes
to produce the results to a spooled file.  Using the SQL UDF I let it
run for over 15 minutes before giving up and deciding it was not worth
it to use the UDF.  This test only translated one date (e.g. 20060629)
per record. 

I found out through searching the web that a SQL UDF is not running SQL
code as though you keyed it in interactive SQL.  It created a CLE
service program from SQLCLE code.  It actually performs a SET command
and I found out that a SET command is actually doing a SELECT ... FROM
QSYS2/QSQPTABL ....  Quite a bit of extra work just to "cast" a numeric
date into a real date.  

I plan on adding a routine to our home grown date utility function that
will convert numeric dates to real dates and then implement those
procedures as UDFS.  I want to see if those perform better.  I much
prefer using 
MyDate = YYMDToDate(DateNum)  
   verses
MyDate = Date(Substring(Digits(DateNum), 1, 4) Concat '-' Concat
                          Substring(Digits(DateNum), 5, 2) Concat '-'
Concat
                          Substring(Digits(DateNum), 7, 2 )).  
Just not at the expense of performance and it was very noticeable
performance loss.  

My tale is of trying out SQL UDFs carefully over a lot of record before
proceeding to using them in production work. For us the SQL UDF was a
failure.  I know it was not the activity of the system since it was the
same approximate loss of performance at many different times of the day.


Thanks, Matt


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Wilt, Charles
Sent: Thursday, June 29, 2006 10:58 AM
To: Midrange Systems Technical Discussion
Subject: RE: AW: SQL - Calculate the number of hours between 2
timestamps

Matt,

I've not ever noticed a 10x slowdown when using UDFs.

Here's my version:
create function CvtNumISOtoDate(indte Numeric(8,0)) returns Date  
language SQL                                                           
not fenced                                                             
deterministic no external action                                       
returns null on null input                                             
contains SQL                                                           
set option datfmt=*ISO