I think this is slowing you down
So I retrieve only 19 records per close-&-then-open
I believe you loop & fetch next 19 times .. loading a page
on page down repeat process loading the next page
If you need to move up in the cursor you will need to open cursor as “scrolable” and then you can
fetch relative :myRecNo;
Hopefully an RPGSQL guy/gal will chime in with corrections/more details.
you say “the time to retrieve the new set of records with the record I am searching for on top of the subfile take more than a minute” and that makes me wonder if you have an index on the search criteria.
Since you know your sub-file will only display 19 rows at a time, you could fetch 19 rows into an array and include the statements “OPTIMIZED FOR 19 ROWS” on the DECLARE and “FETCH FIRST 19 ROWS” on the FETCH statement . That should improve the performance. If you try to fetch past the 19 rows, you will get and SQLSTATE of 02000.
I would guess that your largest performance issue is not around the fetch though. It’s likely around the OPEN statement.