> 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

As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2022 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.