RE: Help improve performance of RPG program

> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Alan Campin
> Sent: Wednesday, May 25, 2005 4:58 PM
> To: rpg400-l@xxxxxxxxxxxx
> Subject: RE: Help improve performance of RPG program - Long Post
> 
> 
> >>Alan,
> 
> >> I think you're going overboard.  Granted UDFs are very 
> useful, but in this case 
> >> I'd say they are the wrong solution.
> 
> >> UDFs are supposed to be "quick" functions.  Having a long 
> running UDF can be a 
> >> problem.
> 
> >> If you wanted to do this strictly in SQL without using 
> RPG, I'd put it into one 
> >> or more Stored Procedures.
> 
> UDF can be short or long running. For example, UDF's can 
> return a table so you might call a UDF and have it processing 
> for hours to produce a result table or you might have a 
> statistical function that does millions of calculations and 
> returns a single number. Really no limit on what they can do. 
> For low latency applications (very little time in 
> application), you definitely need service programs. For high 
> latency (spending a lot of time in the called application), 
> it does make much difference.  

Actually, the UDF itself shouldn't be long running.  From the IBM manual:

 UDFs should not perform operations that take a long time (minutes or hours).
 UDFs are invoked from a low-level in DB2 that holds resources (locks and 
seizes) for
duration of the UDF execution.


So, per IBM, your two example UDFs above would be a bad idea.


> 
> As to quick, what you would be talking about here would be 
> quick. I timed it out on our machine once and it was taking 
> about .00000054 seconds to make a single procedure call to a 
> service program. Writing a record to a IFS file is extremely 
> fast. You are just calling a procedure in a service program 
> and writing a few dozen bytes to a stream file. I have not 
> run any timing tests lately but I know it is extremely fast. 
> Anybody done any timing tests on IFS performance?  

I was thinking that your were talking about a single UDF to write all out 1.5 
million records.

If the UDF writes one record at a time, and the IFS file is left open between 
calls, it shouldn't be a problem.

> 
> Implement this with a regular program, and yes, you are 
> talking slow. Use a service program and you looking at fast, 
> fast, fast. My boss implemented my AS400DATE function using 
> SQL programs and it was taking hours vs. minutes without the 
> UDF. He just abandoned the idea but with my AS400DATE using a 
> service program, I can't even see a difference.  
> 
> Only issue that comes up is returning the one byte and 
> distinct. If the operating system tries to write a single 
> record into a temp file for every record it processes, that 
> could really slow things down. 
> 
> Like I said, I am going to mock it up and she how the system 
> would process. 
> -- 
> This is the RPG programming on the AS400 / iSeries (RPG400-L) 
> mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
> 
>