On my System Power / AS400,
i’m using embedded sql in an RPGLE Program to write a Subfile.
On a large selection the initial fetch is to slow.
When i scroll the speed is good.
Even though i only select 19 rows and fetch 1 row at a time.
Why ?
sqlCmd =
' select ...
offset ? rows +
fetch first '+%CHAR(maxRecnum +1)+' rows only +
) as fuse +
order by rownumber asc +
';
exec sql PREPARE S1 FROM :sqlCMD;
exec sql DECLARE getS1 scroll CURSOR FOR S1;
exec sql OPEN getS1 USING :xy;
OU SQLCODE <> 0 or Recnum1 > maxRecnum +1;
exec sql
fetch getS1
into
:rownumberH
,:Spalte1 :NullIDx_SyvWerte
,:Spalte2 :NullIDx_SyvWerte
,:Spalte3 :NULLIDXW
,:Spalte5 :NullIDx_SyvWerte
,:Spalte6 :NullIDx_SyvWerte
,:Spalte7 :NullIDx_SyvWerte
,:Spalte8 :NullIDx_SyvWerte
,:Spalte9 :NullIDx_SyvWerte
,:Spalte10 :NullIDx_SyvWerte
,:Spalte11 :NullIDx_SyvWerte
,:KYH :NullIDx_SyvWerte
,:FUNKTH :NullIDx_SyvWerte
,:WERTH :NULLIDXH
,:UntermenueKY :NullIDx_SyvWerte
,:KYALTH :NullIDx_SyvWerte
,:SpalteK :NullIDx_SyvWerte
,:MASKH :NullIDx_SyvWerte
,:POSH :NullIDx_SyvWerte
,:WertLaengH :NullIDx_SyvWerte
,:Menu :NullIDx_SyvWerte
,:sMenuLastChgDate :NullIDx_SyvWerte
,:sMenuLastChgTime :NullIDx_SyvWerte
,:sMenuLastCrtDate :NullIDx_SyvWerte
,:sMenuLastCrtTime :NullIDx_SyvWerte
,:WertVergleich :nullIDxWert
,:VarianteAusgelaufen :NullIDx_SyvWerte
,:DatAus
,:id_YSYVH :NullIDx_SyvWerte
,:SbH :NullIDx_SyvWerte
,:LgH :NullIDx_SyvWerte
;
If sqlcode <> 0 or Recnum1 > maxRecnum;
leave;
ENDIF;
Edit:
After the first 19 rows are shown.
The Cursor is closed again.
Then when you scroll the Cursor is opened again.
So it seems like the Problem is with the Fetch.
1
Can you try 2 things please
-
Can you move these 2 lines
offset ? rows +
fetch first ‘+%CHAR(maxRecnum +1)+’ rows only +
To after this line please
order by rownumber asc +
Then you will have,
order by rownumber asc +
offset ? rows +
fetch first '+%CHAR(maxRecnum +1)+' rows only +
For reference , here are 2 examples of SELECTS which you can do which work
select * from qsys2.syscolumns x
order by rrn(x)
offset 0 rows
fetch first 20 rows only
;
select * from qsys2.syscolumns x
order by rrn(x)
offset 19 rows
fetch first 20 rows only
;
-
Can you change your code here after these 2 lines
,:LgH :NullIDx_SyvWerte ;
To this which will increment Recnum1 , like this,
,:LgH :NullIDx_SyvWerte
;
Recnum1 += 1 ;
Thank you
ASIDE:
If that works for you there is a question as to whether your subfile is a page by page subfile – as you mention scrolling was not slow, thanks.
5
What are you counting as the “initial fetch?” Just the FETCH, or the OPEN also?
Most of the time should be in the OPEN, however I’m reasonably sure there’s still some level of overhead that doesn’t happen till you actually start requesting the data.
In any case, you shouldn’t be fetching one row at a time from any RDBMS. If you really want to do pagination like this, you should be retrieving 19 rows at a time.
Personally, now-a-days I don’t bother with page at a time subfiles and simply load all the rows at once. If I expect there to be more than 9999, I let the user know that they should include more filters to limit the number of rows. If your users have to page through 9999+ records, 19 at a time, then you’ve provided a poor application.
1
@The Impaler that was the problem.
I used row_number() over(order by … ) as rownumber.
So it had do go over the whole selection.
When i didn t use order by at all it was fast.
So i changed the way i ordered my joins.
And i now use:
offset ? rows + fetch first '+%CHAR(maxRecnum +1)+' rows only + )
as fuse ORDER BY '+%TRIM(order_by)+'
istead of creating a rownumber.