RE: Performance Issue using Dynamic Calls. Was more questions on UDF.

Alan,
I, for one would appreciate a copy of those programs.
Please send to tehuff@xxxxxxxxxxxxx
Or let me know how to get them.
Thanks
Tom

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Alan Campin
Sent: Wednesday, May 09, 2007 7:29 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: Performance Issue using Dynamic Calls. Was more questions on UDF.

<snip>
Surely it won't be *that* bad. After all, the object resolution will
only
take place once; the first time the UDF (program) is called. After that,
it
should be almost as quick as a service program procedure call.
</snip>


As I had indicated, I wrote some test programs to test difference
between a dynamic call and a service program procedure call on my new
machine.

My results:

It is about 25 to 40 times slower to call a dynamic program vs. calling
a procedure in a service program.

My testing:

Results ratio was were about what I had seen before but this 520 is
incredibly fast. If someone wants a copy of my code to try on there
machine just let me know.

I wrote three quick and dirty programs.

1. A service program with one procedure receiving two parms.
2. A regular program receiving two parms. Does not turn on LR.
3. A regular program to call either the service program or the regular
program in a loop.

I increased the number of loops to 50 million and found the following:

1. Calling service program took approx 8 seconds for the 50 million
loops.
2. Calling using dynamic program call took approx 3 minutes 20 seconds.

I submitted to batch on an unloaded machine running V5R3. Our system has
a very light load. I worked on a previous system with extremely heavy
loading so these numbers would go way up. I suspect you would find that
the dynamic calls times would increase much faster than service program
calls.

Running using 10 million I got the following:

1. Call to service program took approx 2 seconds.
2. Call to regular program took 1 minute 20 seconds.

I ran all tests multiple times and got the same results.

On my previous machine, I had seen 28 seconds to process 10 million so
we get an idea of how much faster this 520 is.

Both consumed about 60% CPU so I didn't see the lower CPU using the
procedure calls.

What these numbers do say is with 50 million records that it is
approximately 25 times slower to make dynamic calls vs. making service
program procedure calls and approximately 40 times slower when running
with 10 million records. Why the differences I don't know but it says a
lot about why you want to use service programs.

Obviously if your database has fewer records, the difference might not
be great but remember that when you are use a UDF, you rarely use it
just once in an SQL statement or at least I don't. I usually have
multiple dates that I am converting so any number gets multiplied by the
number of times of dates you convert in your SQL statement.

Also, you have to add any service program time to the dynamic call time
when you are calling a service program and then a dynamic call.

The other down issue is the performance of the JDE date converter and
whether is stay resident or whether it is making other dynamic calls.
The only JDE program I ever looked was a date converter. After looking
at 40 pages of code, I gave up. What did a data converter need that much
code for?

All this could decrease performance by magnitudes.

On the up side, if you define the UDF as Deterministic and you are
seeing a lot of dates with the same value in a row, the number of calls
would drop because SQL will not call the UDF for the same date value as
the last time it was called. What performance increase that you get is
dependent on the distribution of dates in your tables.

The other thing I don't know is how SQL caches the dates. If you use an
UDF to convert three dates in an SQL statement, will it cache one number
and only call if the last value if anyone of the three dates or does it
cache a value for each one of the three? It could even be smart and
build a working set.

The other thing to just mention is that I have found that if you use a
UDF, it will still use the SQE engine.