RE: opnqry efficiency question

[Buck Calabro <buck.calabro@xxxxxxxxxxxxxxxxx>]

Mark Manske wrote:

>I am wondering which way is "truly" more efficient
-snip-
>He has always tried to do opnqry before the program 
>call to trim down the data as far as he can before 
>calling the program
-snip-

Performance questions are always difficult to answer in the absolute.  If
the OPNQRYF runs for a minute, excludes 1 million records and passes 5000
records to the RPG program, then that minute is probably well spent.  If
OPNQRYF spends 100 minutes to omit 2 records and passes 1 million to the RPG
program then you're probably better to skip the OPNQRYF.  These cases are
clear.

What is less clear are the cases where the added OPNQRYF time is greater
than the RPG-decision making time.  For those, I generally think about the
current volume vs. expected volume.  Let's say we have a payroll summary
file, and we need to create a summary report for just this week's payroll.
In January it has a few thousand records.  OPNQRYF time may amount to a few
dozen seconds and the RPG time to process the current week may be another
few dozen seconds.  But by the end of the year, the file has several hundred
thousand records.  OPNQRYF time might be in the 1-5 minute range, but the
RPG time remains the same because it processes the same number of records.
If an RPG program had to plod through each one of those records it would
certainly run longer than the added 5 OPNQRYF minutes.  In any given
situation, the fewer records you process in RPG the faster that program will
run.  

Reducing I/O to an RPG program is indeed a Good Thing.  The database can do
record selection much faster than RPG can.  I wouldn't use OPNQRYF as a
blanket technique for all record selection situations.  Our typical use is
for ad-hoc type reports that run infrequently.  For interactive work I'd
create an access path and use SETLL/READE to reduce the I/O.

Buck Calabro
Aptis; Albany, NY
"Nothing is so firmly believed as
 that which we least know" -- Michel Montaigne
Visit the Midrange archives at http://www.midrange.com
+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---