Re: Performance question of RPG native read vs SQL

[Charles Wilt <charles.wilt@xxxxxxxxx>]

1) that's not quite the right way of wording it.  All the data is still
read off disk (as a block of rows / assuming there are no VARCHAR or LOBs)
But yes, a cursor with only 18 fields would do better than the existing RPG
with 174.  However, RPG could do just as well, using a LF with only those
18 fields and reading into a DS.

You'd need to block read 100+ rows from the cursor into an array to beat
the best RPG could be set to do.

2) Is that all it's doing?  In that case, forget RBAR
(Row-by-agonizing-row) altogether (SQL cursor or RPG )
Simply do a
  INSERT INTO table2
    (select sum(...), sum(...)
      from table1
      group by key1, key2
     )

3) No.

Charles


On Wed, Oct 14, 2020 at 2:01 PM <smith5646midrange@xxxxxxxxx> wrote:

> We have an RPGLE program that I have been asked to evaluate for performance
> issues.  I want to propose some changes to it but I'm having a hard time
> performance testing the changes to prove my theory.  Therefore, I thought I
> would ask the group some questions and make sure that I am not off track or
> misinformed, especially the logic behind the first change that I want to
> make.
>
>
>
> The first change.
>
> The program currently uses native RPG reads (FSpec).  There are 7,328,305
> records in the driver file which contains 174 fields for a total of 1,172
> bytes.  Of those 174 fields, the program only uses 18 of them for a total
> of
> 125 bytes.  It is my understanding when using SQL that if I build a cursor
> selecting these 18 fields, that is the only data the system reads
> (generically speaking) vs reading all 1,172 bytes and extracting out the
> 125
> bytes that it needs so changing to SQL would eliminate reading 1,047 bytes
> per record or about 7.6G.
>
>
>
> The second change.
>
> As it reads the 7,328,305 records, it is reading by the key and
> accumulating
> totals.  When the key changes, it dumps the totals to a summary file,
> clears
> the arrays, and accumulates for the next key.  Would changing this to SQL
> using SUM() and GROUP BY make this faster?
>
>
>
>
>
> I've left out some details but bear with me and look at the what I am
> asking
> and don't try to figure out the link between the above two changes and this
> third change.  It is all linked together but I didn't provide the details
> of
> how.
>
>
>
> The third change.
>
>                 There are 22 arrays in the program for the rolling totals.
> The total of a single occurrence of the arrays is 318 bytes.  Each array is
> dim(32,767) for a total of 10,419,906 bytes.  Since there are a maximum of
> 5,000 index values, I am going to change the dim(32,767) to dim(5,000).
> That reduces the memory consumption of the program by about 8M.  This is on
> a huge machine and I don't know how much memory it has or how it is
> configured but I'm sure it has a lot.  Generally speaking, could a program
> requiring 10,419,906 bytes of memory also be a performance hog even if
> there
> was ample memory in the subsystem?
>
>
>
> Thanks in advance for all of your help and knowledge.
>
>
>
> --
> This is the RPG programming on IBM i (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/rpg400-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: https://amazon.midrange.com