Re: RPGLE Converted into SQL having performance issue

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

+1 what Charles said!

But do get the index support right, as well.

Vern

On 12/15/2020 9:20 AM, 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://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