Re: Performance question of RPG native read vs SQL

Be interesting to create a view that only listed the required fields and just substitute that in the existing program. That would give an indication as to the impact of buffering.

There should be very little overhead in the current program from the unused fields since RPG will not be unloading them from the buffer. So the overhead is from the "wasted" buffer space.

On Oct 14, 2020, at 4:17 PM, Scott Mildenberger <smildenberger@xxxxxxxxxxxx> wrote:

I would try and write a sql query and see if you can get the correct data that way. If you can you can see if any indexes are recommended to speed it up. If the sql query is much faster then change the RPG to get all the data with the one sql query and create the report. If you have a subset of data to use for testing you can write your query using it to validate. With proper indexes that table is not that big compared to some we have and performance of sql with proper indexes is very good.

Scott Mildenberger | Software Developer | Washington Companies IT Services | 101 International Drive | Missoula, MT | Office: 406-523-1536 | www.washingtoncompanies.com

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of smith5646midrange@xxxxxxxxx
Sent: Wednesday, October 14, 2020 2:01 PM
To: Midrange RPG List <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Performance question of RPG native read vs SQL

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 This email is from an external sender. If you don’t know who it is from and if you were not expecting it, DO NOT click any links or input your information. Report this email using the Report Phishing Tool in Outlook.[Cofense Image]
--
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