Re: Performance question of RPG native read vs SQL

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

In my opinion the sql approach has the potential for the most bang for the buck. If you know how long the current program runs then you should be able to get a good feel for whether the sql approach is significantly better or not by running at different times. If only marginal improvement then it probably isn't worth it.

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

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx <mailto:rpg400-l-bounces@xxxxxxxxxxxxxxxxxx>> On Behalf Of smith5646midrange@xxxxxxxxx <mailto:smith5646midrange@xxxxxxxxx>
Sent: Wednesday, October 14, 2020 2:28 PM
To: 'RPG programming on IBM i' <rpg400-l@xxxxxxxxxxxxxxxxxx <mailto:rpg400-l@xxxxxxxxxxxxxxxxxx>>
Subject: RE: Performance question of RPG native read vs SQL

My problem with gathering the performance stats is that the machine is never in a quiet state. Something else could be hammering the machine / DASD / memory during one of the tests and I would be comparing apples and oranges.

That is why I was asking for confirmation that what I wanted to do was based on correct information and should help.

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Scott Mildenberger
Sent: Wednesday, October 14, 2020 4:18 PM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: Performance question of RPG native read vs SQL

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

--
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 <http://midrange.com/> by shopping at amazon.com <http://amazon.com/> with our affiliate link: https://amazon.midrange.com <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 <mailto:RPG400-L@xxxxxxxxxxxxxxxxxx>
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l <https://lists.midrange.com/mailman/listinfo/rpg400-l>
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx <mailto:RPG400-L-request@xxxxxxxxxxxxxxxxxx>
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l <https://archive.midrange.com/rpg400-l>.

Please contact support@xxxxxxxxxxxxxxxxxxxx <mailto:support@xxxxxxxxxxxxxxxxxxxx> for any subscription related questions.

Help support midrange.com <http://midrange.com/> by shopping at amazon.com <http://amazon.com/> with our affiliate link: https://amazon.midrange.com <https://amazon.midrange.com/>