RE: RPGLE Converted into SQL having performance issue -- RPG400-L

Great news Ashwani

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them and keeping them!"
„Train people well enough so they can leave, treat them well enough so they don't want to.“ (Richard Branson)

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Singh, Ashwani (Lake Mary) via RPG400-L
Sent: Dienstag, 22. Dezember 2020 01:14
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Cc: Singh, Ashwani (Lake Mary) <Ashwani.Singh@xxxxxxxxxx>
Subject: RE: RPGLE Converted into SQL having performance issue

Thanks Birgitta,

With all the changes suggested here and few others I was able to get significant improvement in time.

VE shows huge benefits after making the below changes !!!

1) Changed SQL to just fetch the single column, which was required for my processing.

2) Changed SQL to use PF instead of logical

Will try to add multiple Fetch logic as well and see how it behaves.

Thanks to all of you amazing people!

Merry Christmas, Happy New Year and Happy Holidays!

Thanks
Ashwani

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Birgitta Hauser
Sent: Friday, December 18, 2020 12:17 AM
To: 'RPG programming on IBM i' <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: RPGLE Converted into SQL having performance issue

⚠ EXTERNAL MESSAGE – Think Before You Click

Singh,

it is very had to say what really happens without seeing what really happens and without analysis, so we can only guess.
In Release 7.3 all SQL Statements should be performed by the SQE.

current FILE_YY is the index file to select only the records that I am
interested in processing

Does this mean you are using a logical file with SELECT/OMIT clauses?
This could be the problem and also the reason why an SQL Statement is (still) executed by the CQE.
If so try to write a SQL query based on the physical files with the appropriate where conditions.
If it runs fast enough you can copy this query in your program (or even better create an SQL View which includes the query and execute the view in your program.) BTW a view is only a stored SELECT statement and never has an access path.
If it does not run fast enough. Run it through visual explain and analyze it (you may get perhaps an index advice).
But it is hard to explain exactly what to do, if you do not have any experience with it and I can only guess.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown) "If you think education is expensive, try ignorance." (Derek Bok) "What is worse than training your staff and losing them? Not training them and keeping them!"
„Train people well enough so they can leave, treat them well enough so they don't want to.“ (Richard Branson)

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Singh, Ashwani (Lake Mary) via RPG400-L
Sent: Mittwoch, 16. Dezember 2020 22:01
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Cc: Singh, Ashwani (Lake Mary) <Ashwani.Singh@xxxxxxxxxx>
Subject: RE: RPGLE Converted into SQL having performance issue

Thanks Birgitta,

We are on V7R3.

This SQL I am very much worried about.... the other one process less than 100 records ( Though that is inefficient too I know!)

1 ) I will change the * to the fields I need
2) Should I change the logical to PF and select the records I need, the current FILE_YY is the index file to select only the records that I am interested in processing.
3) Any other changes other than above 2 ? Multiple row fetches?

C/EXEC SQL DECLARE DAT_FILEXX CURSOR FOR
C+ SELECT *
C+ FROM FILEXX
C+ ORDER BY XX0097
C+ FOR READ ONLY
C/END-EXEC
C/EXEC SQL OPEN DAT_FILEXX
C/END-EXEC
C*

This is fetch
C/EXEC SQL FETCH DAT_FILEXX INTO :FILEXXREC C/END-EXEC

Thanks,
Ashwani Singh Software Development Engineer,Sr Bank Solutions (Conversions/Implementations)
Office: 407-357-4550 | Mobile: 407-416-2128 Fiserv Helping Small Businesses Get Back2Business Fiserv | Join Our Team | Twitter | LinkedIn | Facebook FORTUNE World's Most Admired Companies® 2014 | 2015 | 2016 | 2017 | 2018 | 2019 | 2020

© 2020 Fiserv Inc. or its affiliates. Fiserv is a registered trademark of Fiserv Inc. Privacy Policy © 2020 Fortune Media IP Limited. Used under license.

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Birgitta Hauser
Sent: Wednesday, December 16, 2020 1:17 AM
To: 'RPG programming on IBM i' <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: RPGLE Converted into SQL having performance issue

⚠ EXTERNAL MESSAGE – Think Before You Click

Just one question on what IBMi Release you are on?
Since Release 7.2 (almost) everything should be executed by the SQE (even native I/O 😉)
... and CQE optimizes differently than SQE With the CQE you can get an optimization timeout so that the optimizer cannot find the best index at time and decides for a table scan (except you specified a logical file then in a time out this logical file can/will be taken).
But the first goal would be to bring the queries to SQE.

Also are there indexes (or even logical files) with the key XX0055, XX0097 and for the second file HCNRPRM.
In the first query the optimizer (independent of CQE or SQE) should be able to use the first index.
In the second query a table scan must be performed, because there is no WHERE condition. If there is no index over the second file for the order by, the result has to be sorted on the fly which is time consuming (especially with a scroll cursor).
If you do not have to read forward and backward within the same result set (for examples if you only want to read and display or print), do not use a scroll cursor.
The scoll can also prevent the optimizer to use an index.
Also avoid to use SELECT * but select only the columns you need.

If you use a single or multiple row fetch does not make a big difference (even though a multiple row fetch is faster), the most time consuming part of executing a cursor is the OPEN statement, because the SQL query is executed there.
A fetch (independent of a single or multiple row fetch) will only return the data that is already loaded in the memory.

You may STRDBG (without any other specification) before running your (embedded SQL) RPG program. With this command SQL messages are added to the joblog. Just have a look at the joblog then.

saw SQL hogging the whole system, processing about 3000 records in 45
minutes

Seems to me either a (old/CQE) temporary index is build over the huge file (which is very time consuming) or the data in the huge file are sorted directly because of the SCROLL cursor.

How fast are the queries if you run them with ACS Run SQL Script (or STRSQL).

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown) "If you think education is expensive, try ignorance." (Derek Bok) "What is worse than training your staff and losing them? Not training them and keeping them!"
„Train people well enough so they can leave, treat them well enough so they don't want to.“ (Richard Branson)

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Singh, Ashwani (Lake Mary) via RPG400-L
Sent: Mittwoch, 16. Dezember 2020 01:36
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Cc: Singh, Ashwani (Lake Mary) <Ashwani.Singh@xxxxxxxxxx>
Subject: RE: RPGLE Converted into SQL having performance issue

Thanks so much all Charles/Frank/Vern and others, this code was given to me recently. I had worked with SQL and have always used sets or if at all needed multiple fetch and knew something was wrong there.

Yesterday was sitting with the client and saw SQL hogging the whole system, processing about 3000 records in 45 minutes :(

So this logical was picked up (I know it was Devs bad decision) to use LF, one record at a time fetch and this file has trigger as well as it contains PCI data.

I ran Visual Explain and it never used SQE rather used CQE because of existing triggers.

I can bring a block of data and remove cursor as that is really not required, but this trigger has raised another question should I use SQL at all ?

If I bring block of data how many rows are ideal to fetch in one shot!

As always appreciate you guys very much!

Thanks,
Ashwani -----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Justin Barrett
Sent: Tuesday, December 15, 2020 7:10 PM
To: rpg400-l@xxxxxxxxxxxxxxxxxx
Subject: Re: RPGLE Converted into SQL having performance issue

⚠ EXTERNAL MESSAGE – Think Before You Click

Cannot agree more with this. Cursors are expensive, row at a time cursors doubly so, scroll cursors row at a time and just forget about your program having any kind of performance.

If you absolutely must work row at a time, stick to native I/O.

If you do use cursors then always pull batches of records, never record at a time.

Only use scroll cursors if you must randomly move around the data backwards and forwards.

And finally, if you are pulling data from a table, try not to use * and instead list the columns that you actually need.

Without know more about what's going on, the best bet is to just stick to file I/O unless there's some way to do your operations in bulk.

I don't know what you've got going on but if you're using that :YY0008 as a predicate in your first query a lot, you may want to look at where the value going into that parameter is coming from and if it's already in a table in of itself, you can always do a inner join on that source with this FILEXXX with that YY0008 as the join key. But it would really depend on where that's coming from.

Cheers

On 12/15/20 09:20, Charles Wilt wrote:

Not really surprising, you've replaced one access method with another
that has more overhead.

You can't simply replace RPG 1 record-at-time with SQL 1 record-at-time.
Better off to stick with RPG.

Used properly, SQL is faster than RPG. But you have to use it
properly; which means working with SETS of records.

- unless there's no other option, don't use cursors. (Display &
printing they are required)
- if using a cursor, read in blocks of records (100+)

You might find this presentation I gave a few years ago helpful..
https://urldefense.proofpoint.com/v2/url?u=https-3A__www.linkedin.com_
posts_charles-2Dwilt-2D2a4a303-5Fsql-2Dfor-2Dthe-2Drpg-2Ddeveloper-2Da
ctivity-2D6641756015354355712-2DwoZR&d=DwIGaQ&c=rE3mhBYFJfJGqQ7WI0-DPw
&r=FfwKOXXYX1q9F5u-iJzZSViRPIoMS-xj5OEEpmrlGdQ&m=Q4TNJi0OHJ_YUBqh3ST8E
M3vnNvaifNTqw20-u-nPw4&s=DIOflfIQy7VAl7N6qAxoQ_HDmfjXlCsfEKpBdjO9h4I&e
=

Charles

On Tue, Dec 15, 2020 at 1:33 AM Singh, Ashwani (Lake Mary) via
RPG400-L < rpg400-l@xxxxxxxxxxxxxxxxxx> wrote:

Hi Guys,

We converted our one of the main module for transaction processing
from RPGLE to SQLRPGLE and we are getting a lot latency and low
performance issue.

We are thinking of going back to native IO, please let us know what's
wrong with below and is there anything we can do to improve the performance.

Here are the SQL that have been added , this one process about 40-50K
records daily.

C/EXEC SQL DECLARE DAT_FILEXX CURSOR FOR

C+ SELECT *

C+ FROM FILEXXX WHERE XX0055 = :YY0008

C+ ORDER BY XX0097

C+ FOR READ ONLY

C/END-EXEC

C/EXEC SQL OPEN DAT_FILEXX

C/END-EXEC

This one process about 100 - 500 records

C/EXEC SQL DECLARE DAT_FILEYY DYNAMIC SCROLL CURSOR FOR

C+ SELECT *

C+ FROM FILEYY

C+ ORDER BY HCNRPRTI

C+ FOR READ ONLY

C/END-EXEC

C/EXEC SQL OPEN DAT_FILEYY

C/END-EXEC

PRTSQLINF

CRTSQLRPGI

OBJ(QTEMP/XXF0170)

SRCFILE(LIBX/USSORC)

SRCMBR(XX0170)

COMMIT(*CHG)

OPTION(*SYS *NOEXTIND *SYSTIME *PERIOD)

TGTRLS(V7R2M0)

ALWCPYDTA(*OPTIMIZE)

CLOSQLCSR(*ENDACTGRP)

RDB(*LOCAL)

DATFMT(*MDY)

DATSEP('/')

TIMFMT(*HMS)

TIMSEP(':')

DFTRDBCOL(*NONE)

DYNDFTCOL(*NO)

SQLPKG(LIBYY/XX0170)

MONITOR(*USER)

SQLCURRULE(*DB2)

ALWBLK(*ALLREAD)

DLYPRP(*NO)

DYNUSRPRF(*USER)

USRPRF(*OWNER)

SRTSEQ(*HEX)

LANGID(ENU)

RDBCNNMTH(*DUW)

TEXT('Authorization

SQLPATH(*LIBL)

DECRESULT(31 31 0)

DECFLTRND(*HALFEVEN)

CONACC(*DFT)

STATEMENT TEXT CCSID(37)

DECLARE DAT_FILEXX CURSOR FOR SELECT * FROM FILEXX WHERE XX0055 = : H
ORDER BY

XX0097 FOR READ ONLY

SQL4021 Access plan last saved on 12/12/20 at 08:01:32.

SQL4020 Estimated query run time is 0 seconds.

SQL4017 Host variables implemented as reusable ODP.

SQL4002 Reusable ODP sort used.

SQL4007 Query implementation for join position 1 table 1.

SQL4008 Index FILEXXLE used for table 1.
--> This is an Index

SQL4026 Index only access used on table number 1.

DECLARE DAT_FILEYY DYNAMIC SCROLL CURSOR FOR SELECT * FROM FILEYY
ORDER BY

HCNRPRTI FOR READ ONLY

SQL4021 Access plan last saved on 12/12/20 at 08:01:32.

SQL4020 Estimated query run time is 0 seconds.

SQL4009 Index created for table 1.

SQL4026 Index only access used on table number 1.

SQL4008 Index MTI(16) used for table 1.

Thanks,

Ashwani
--
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://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange.c
om_mailman_listinfo_rpg400-2Dl&d=DwIGaQ&c=rE3mhBYFJfJGqQ7WI0-DPw&r=Ff
wKOXXYX1q9F5u-iJzZSViRPIoMS-xj5OEEpmrlGdQ&m=Q4TNJi0OHJ_YUBqh3ST8EM3vn
NvaifNTqw20-u-nPw4&s=63VfN2SkcL4YookUFxuyucrshpX4MH1c4gL5Z14i5_o&e=
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_rpg400-2Dl&d=DwIGaQ&c=rE3mhBYFJfJGqQ7WI0-DPw&r=FfwKOXXYX1q9F5u-iJzZSViRPIoMS-xj5OEEpmrlGdQ&m=Q4TNJi0OHJ_YUBqh3ST8EM3vnNvaifNTqw20-u-nPw4&s=3Daw3FyEwGDpTxC3zUFUM7Edr2b5FTOnWmGH-oMLONw&e= .

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related questions.

Help support midrange.com by shopping at amazon.com with our
affiliate
link:
https://urldefense.proofpoint.com/v2/url?u=https-3A__amazon.midrange.
com&d=DwIGaQ&c=rE3mhBYFJfJGqQ7WI0-DPw&r=FfwKOXXYX1q9F5u-iJzZSViRPIoMS
-xj5OEEpmrlGdQ&m=Q4TNJi0OHJ_YUBqh3ST8EM3vnNvaifNTqw20-u-nPw4&s=VpEeXj
KcRrLYIKENJGsb4jozPFginIibVxMZhf3AO6U&e=