Re: Performance question of RPG native read vs SQL -- RPG400-L

Heck, depending on how the summary file is used...you might not even need
it.

I'd look at building a EVI index that includes the aggregate SUM() you need.

Now build a SQL UDTF/view over the base table with
select sum(...), sum(...)
from table1
group by key1, key2

You can use VE to check that reading from the view/UDTF is returning data
from the EVI

Charles

On Wed, Oct 14, 2020 at 3:18 PM Charles Wilt <charles.wilt@xxxxxxxxx> wrote:

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