Re: Program performance incease ideas

Good Morning

A few ideas (I'm a "speed-freak", and the iSeries platform has many 
techniques):

-- Your SQL statement is summarizing a subset of records, based on part 
number and color. Perhaps you could create a view that summarizes the file 
based on these fields first, copies the summarized veiw to a file (PF), 
then, during your DOW loop, CHAIN to the temporary table

-- The table your SQL statement(s) are running against -- is it indexed by 
part number and color? I am not sure if using an LF with these keys will 
speed up SQL (probably will), but an index over the PF might be faster

-- Is MyFile a large file? is the record length large? You should 1) be 
certain blocking is done, and 2) override to optimize blocking (OVRDBF 
FILE(MYFILE) SEQONLY(*YES ####), where #### is the number of records that 
will fit in 128k (rec length/128000)

On 7/19/05, Brian Piotrowski <bpiotrowski@xxxxxxxxxxxxxxx> wrote:
> 
> 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
> 
> -=-=-=-=-=-=-=-=-=-=-=-=-=-
> 
> 
> 
> --
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
> 
>