Re: RPGLE Converted into SQL having performance issue

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