RE: sql help-select sum of 2 fields in flat file

["Joe Pluta" <joepluta@xxxxxxxxxxxxxxxxx>]

> From: Jim Franz
>
> this sql is in a rpg inquiry program over ar invoice file. The problem is
> performance. Over a million record file, so I can't read all records,
> summing the 2 fields in each record.

Jim, as far as I know, no matter what you do, that's exactly what is going
to happen - whether it's SQL or RPG or MI, in order to select or not select
a record, SOMEBODY will have to read the record, sum the values and decide
whether to process the record.

While it may be a littler faster under the covers of SQL because (from what
I understand) the SQL stays "under the MI layer" while reading the records,
at some point you still have to fetch every one of those 2,000,000 records.

If you can't touch the database, then add an extension file that has just
the record key and the sum of the two fields.  And if you can't touch the
program that updates the file, then add a trigger to the file that in turn
updates the extension file.  Either way will outperform the SQL approach,
probably by orders of magnitude.

If you have a large number of records and a fixed selection criteria, then
please use the database where it is at its best: logical views.

Joe Pluta
www.plutabrothers.com