Re: Embedded SENSITIVE DYNAMIC SQL cursor question -- RPG400-L

Generally speaking, both SENSITIVE and INSENSITIVE are detrimental to
performance, as you are forcing the DB down a single path.

ASENSITIVE lets the DB decide which to use.

Having said that, SENSITIVE should perform just fine as long as you have
the right indexes.

However, do you really need SENSITIVE SCROLL?

If you're opening the cursor and reading the entire set into a load-all
subfile, then ASENSITIVE NO SCROLL is sufficient. Simply close and reopen
the cursor when the user refreshes.

If using a page-at-time subfile, then SENSITIVE SCOLL means when the user
pages backwards, they might not get the same set of records they saw the
first time. If that's really what you want, fine.

In any UI other than twinax attached 5250 over a dial-up line, page-at-a
time or even expanding subfile makes little sense IMHO :)

Personally, I'd encapsulate the SQL statement in a stored procedure, view
and/or UDTF rather than directly embedding it in an RPG program. Many of
IBM's new services are view's generated by UDTFs ... there's a good reason
for that.

Charles

On Thu, Apr 21, 2022 at 6:01 AM Alan Cassidy <cfuture@xxxxxxxxxxx> wrote:

I've searched around but haven't found clarity to get my understanding
confirmed on this.

I'm working on getting to a solution to a horrendous performance problem
in a set of programs that show different views to data that changes all
day that a team is working on with customers. It is important that what
they see is the most currently available status of the data.

I converted the primary file read that is input to populate the subfile
to an SQL cursor as the first step but it's not enough. So now I'm doing
a cursor with somewhat complex join logic including a couple of common
table expressions, WITH clauses, and so on.

The same data set is used in various programs that they navigate.

I have changed the first program called from the menu from ACTGRP( *NEW
) to ACTGRP( XYZ ), and I've changed most of the first programs called
from there to ACTGRP( *CALLER ). I am grateful for the response I got on
this forum (and/or the midrange forum) for helping me to decide on a
named activation group. And THANK YOU DAVID for creating these lists and
managing them!

This is the idea I'm asking for confirmation for:

Now I'm proceeding with the idea of using a service program procedure to
create a SENSITIVE DYNAMIC SCROLL CURSOR. That way it gets real-time
changes all day. So first time in, the SQL cursor gets created, and the
code reads it start to finish to populate the subfile. When the user
refreshes, the FETCH FIRST returns the "pointer" to the first row and
then FETCH NEXT reads thru the cursor and I can populate the subfile
from that.

That should reduce performance time, should it not?

Sorry if this is more words than necessary,

---Alan

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