|
Brian, One approach could be to sum all records first then match the two. The main problem I see if that the query engine MUST cover the indexes for each read of MyFile. How about: DECLARE SUMCSR CURSOR FOR SELECT summary data FROM file group by FIELDS,,, OPEN SUMCSR DO WHILE SQLSTT = '00000' FETCH FROM SUMCSR CHAIN TO MYFILE UPDATE SUM FIELD ENDDO This way the query engine will only go over the index one time. Thank you, Matt Tyler WinCo Foods, LLC mattt@xxxxxxxxxxxxxx -----Original Message----- From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Brian Piotrowski Sent: Tuesday, July 19, 2005 8:10 AM To: RPG programming on the AS400 / iSeries Subject: Program performance incease ideas Hi All, I have a portion of one of my programs that does this: Read Myfile Do while not eof(Myfile) Read Myfile SQLSum = Get the sum of a series of records where somepart = current part# and somecolour = current part colour NewSum = CurrentSum - SQLSum Update Myfile Read Myfile EndDo I find this process to be very time-consuming, as each SQL transaction takes about 2-7 seconds to complete. I'm using a logical file to get the data that is keyed on the part number and colour as well as only selecting records that have a status of 3 or 5. Any ideas on how I could speed this up? The physical file which it is updating only has 1400 records in it, and it's been running now for the past ½ hour and has less than ½ the records processed. Thanks, Brian. -=-=-=-=-=-=-=-=-=-=-=-=-=- Brian Piotrowski Specialist - I.T. Simcoe Parts Service, Inc. Ph: 705-435-7814 x343 Fx: 705-435-6746 bpiotrowski@xxxxxxxxxxxxxxx -=-=-=-=-=-=-=-=-=-=-=-=-=-
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 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.