System Power / AS400 - Embedded sql Cursor initial Fetch is slow

Question

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.

order by rownumber asc -- How is this rownumber computed? Seems like the logic may not be trivial for the engine to process. — The Impaler
– The Impaler, Commented Sep 4, 2024 at 12:21

stenag · Accepted Answer · 2024-09-04 14:35:03Z

0

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.

edited Sep 4, 2024 at 14:35

answered Sep 4, 2024 at 14:02

stenag

1144 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Flow Over a year ago

I tried it. It didn t improve the performance. The subfile should be page by page. Since i only fetch 19 rows with the cursor.

stenag Over a year ago

Only seeing it is a scroll cursor now. I don't think you need the offset/fetch first... in the sql statement. But the next thing would be to check recnum1 and maxrecnum are doing what you expect and how many times cursor is opened and closed. Feels like you might be getting everything the first time.

Flow Over a year ago

The Cursor is opened. Then first position is fetched (this takes longer) Then the other 18 Positions are fetched one by one (that s faster already) Then Cursor is closed. => Scroll Then Cursor is opened. Then first fetch = it s faster already. And so on. I looked at the fetch syntax. If i don t define anything extra it should be a single row fetch. Witch should only fetch one. And my Cursor selection also only holds 19 rows.

stenag Over a year ago

Not what I was thinking at all then. You could try (and I don't know if it will work) , before loop just the first time after the first open only, "fetch first getS1..." . fetch first is for scroll cursors.

stenag Over a year ago

I've been no help to you. I would check the job log while the slow first fetch is running. If in test increase logging for your job if you can. SQL may be putting a lot in the joblog . If it is and you look into it SQL puts suggestions for improving performance eg. suggested logicals . Good luck.

Charles · Accepted Answer · 2024-09-04 14:38:16Z

0

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.

answered Sep 4, 2024 at 14:38

Charles

24.2k3 gold badges23 silver badges50 bronze badges

1 Comment

Flow Over a year ago

Loading 9999 records directly would improve the speed the next pages are shown. But that's not my problem.

Flow · Accepted Answer · 2024-09-06 08:16:45Z

0

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

edited Sep 6, 2024 at 8:16

answered Sep 6, 2024 at 8:15

Flow

215 bronze badges

Collectives™ on Stack Overflow

System Power / AS400 - Embedded sql Cursor initial Fetch is slow

3 Answers 3

5 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related