RE: Program performance incease ideas

Hi Tony,

Thanks for the ideas.

The table by which the SQL statement is running against is indexed using partno 
and partcl as part of the key (there are a few more fields involved as well).

Myfile ranges between 1500 and 3500 records, so it's no abnormally large.

I thought about using a view on the data - something to review!

Brian.

-----Original Message-----
From: Tony Carolla [mailto:carolla@xxxxxxxxx] 
Sent: Tuesday, July 19, 2005 10:24 AM
To: RPG programming on the AS400 / iSeries
Subject: 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.
> 
>