Re: RPGLE Converted into SQL having performance issue

[Charles Wilt <charles.wilt@xxxxxxxxx>]

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://www.linkedin.com/posts/charles-wilt-2a4a303_sql-for-the-rpg-developer-activity-6641756015354355712-woZR

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://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