It's hard to tell what you're really doing, but, it appears that you're using one cursor to feed results into another cursor that is opening and closing multiple times. You're also declaring one cursor as dynamic scroll which can add a little processing.
It sounds like you're new to SQL, so this can be a bit of a challenge, but, it would be much better processing wise if you could join the two queries into one query. The optimizer performs much better when you give it the whole picture, rather than trying to manage it in little chunks of multiple query open/close.
-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Singh, Ashwani (Lake Mary) via RPG400-L
Sent: Tuesday, December 15, 2020 3:33 AM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Cc: Singh, Ashwani (Lake Mary) <Ashwani.Singh@xxxxxxxxxx>
Subject: RPGLE Converted into SQL having performance issue
CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe.
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
midrange.com
