> 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
As an Amazon Associate we earn from qualifying purchases.
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.