Re: Performance question of RPG native read vs SQL

Unfortunately, the entire driver file is needed to build the summary file.
The driver is a transaction based file. It is rolled up to the summary
level so they don't have to chug through the transactions every time.
There
is no selection. Only a top to bottom keyed read.

I have tried to performance test this. The first day it ran for 3 hours.
The next day it ran for 1.5 hours. The third day was a little over 2
hours.
Same data, different durations. Apples, oranges, and bananas because of
system usage.

I guess unless I hear a "no, don't do that" from someone by tomorrow
morning, I will start diving into the swap to SQL.

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
DEnglander--- via RPG400-L
Sent: Wednesday, October 14, 2020 4:47 PM
To: rpg400-l@xxxxxxxxxxxxxxxxxx
Cc: DEnglander@xxxxxxxxxxxxxxx
Subject: RE: Performance question of RPG native read vs SQL

I agree with what Scott wrote.

Also, try also to focus on record selection in the RPG program. I worked
with a program once that read a HUGE G/L file row by row. It was reading
billions of rows and turns out it only selected less than 10% of those.
When SQL was used, the selection was alot faster since only a small amount
of data was actually selected for processing instead of the whole file.
I'd say that the record selection may be what the culprit in your case.
Personally, I would not worry about how many bytes are actually used. I
would get SQL to pull in what you need and do the summary too. Just make
sure the indexes are adequate. At least have them on what you are selecting
by. If you can do that, and read the SQL cursor fetching 400+ rows at a
time, you'll most likely see improvement.

Another thing to look at is a driver file, if you have it. The program I
referenced above was driven by another file whose contents determined what
to select, but the program slogged through the whole file each time.
Linking the driver to the selection also sped it up too. I don't remember
the specifics, but the improvement in runtime was definitely noticeable by
IT and the users.

Hope this helps,

Doug


"CONFIDENTIALITY NOTICE: This e-mail transmission (and/or the attachments
accompanying it) contain confidential information belonging to the sender.
The information is intended only for the use of the intended recipient. If
you are not the intended recipient, you are hereby notified that any
disclosure, copying, distribution or the taking of any action in reliance
on
the contents of the information is strictly prohibited. Any unauthorized
interception of this transmission is illegal under the law. If you have
received this transmission in error, please promptly notify the sender by
reply e-mail, and then destroy all copies of the transmission."
--
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 by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com