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.  

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?  

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.