× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. RPG400-L
  3. October 2020

Re: Performance question of RPG native read vs SQL

I'm jumping in late here - you can create an index that has the key columns you need, plus it can have the 18 fields you are using. It can even have a WHERE clause, but it doesn't seem you need that.

The thing is, you CAN use this in an F-specification, which is pretty cool - so it's a lot like an LF as was described by a couple people here.

But the other use is with SQL - although you can't use it in a SELECT * from index, the optimizer CAN use it to get the result set created most efficiently. There used to be a setting, IGNORE_DERIVED_INDEX, that would allow using select/omit LFs and indexes like this, where expressions are used where columns can be used - I believe that IGNORE_DERIVED_INDEX is no longer settable - all indexes like this can be used for optimization now. Basically, if you have been using an expression in your program to get a value from the file, you can put it into and INDEX - the optimizer is likely to use something that matches what you are asking for.

HTH
Vern

On 10/14/2020 6:11 PM, Alan Campin wrote:
May I suggest you simply write an SQL statement to summarize the data as
you do in the program and just run it interactively and see how long it
takes to produce results. Anything you do in the database engine should be
quicker than reading record by record through the record interface.


On Wed, Oct 14, 2020 at 4:04 PM Jon Paris <jon.paris@xxxxxxxxxxxxxx> wrote:

Could also test the basic performance profile by taking a subset of the
data and extrapolating from that. It didn't;t sound as if the nature of the
process precluded that.


On Oct 14, 2020, at 4:39 PM, Scott Mildenberger <
smildenberger@xxxxxxxxxxxx <mailto: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 <mailto:
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 <mailto:
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 <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:01 PM
To: Midrange RPG List <rpg400-l@xxxxxxxxxxxxxxxxxx <mailto:
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 <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/> 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/>
--
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/> 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/>
--
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



As an Amazon Associate we earn from qualifying purchases.

This thread ...
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.