Ask Reuben

ON FILL BUFFER

What performance improvement is possible around arrays?

How can my program use less memory?

What is ON FILL BUFFER?

One of the key characteristics of the Dynamic User Interface and the Front-End protocol is that only the visible rows of a a static or dynamic array rendered via List Dialogs (DISPLAY ARRAY / INPUT ARRAY)  to TABLE / TREE /  SCROLLGRID views are sent from the back-end to the front-end.  That is if an array has 1,000 rows but only 20 rows are visible, only 20 rows will be sent from the back-end to the front-end.  As the user scrolls down, then additional rows are sent from the back-end to the front-end.   No resources are wasted by sending rows from the back-end to the front-end that the user cannot see.

That same strategy can also be applied to fetching rows from the database into the array variable.  Why use resources to fetch rows from the database table if the user is not going to look at them?  This is achieved by use of the ON FILL BUFFER block and is known as Paged Mode of a DISPLAY ARRAY.   Using the same numbers as above, if only 20 rows are visible then only 20 rows will be fetched from the database into the array variable and then sent to the front-end.

As a result of using ON FILL BUFFER. the number of rows fetched from the database is a lot smaller, and the amount of memory used by the Genero application is smaller as well.  In both cases, these resources are only used for what the user can see.  What the user cannot see is not fetched from the database and what the user cannot see is not consuming memory.


StandardUsing ON FILL BUFFER
Rows in database that match search criteria10001000
Array variable populated with this many rows100020
This many rows sent across network to front-end2020

To code using ON FILL BUFFER involves adding the ON FILL BUFFER block to the DISPLAY ARRAY, and in here is where you have the code to populate the array variable.  You do not populate the array variable before the DISPLAY ARRAY.  In this block you make use of fgl_dialog_getBufferLength() and fgl_dialog_getBufferStart()  to determine how many rows are visible and what is the first visible row.  You typically use these then in a small loop containing a FETCH ABSOLUTE database cursor to retrieve the currently visible rows.

The rendering of the vertical scrollbar is dependent on knowing the total number of rows in the array.  If you don’t know the total number of rows, set COUNT=-1.  Otherwise if you are able to do a SELECT COUNT and determine the total number of rows, then this can be set via the COUNT attribute, set_Count(),  or ui.Dialog.setArrayLength.

If you do want sorting, you can manage that in the ON SORT block and use of the Dialog.getSortKey() and DIALOG.isSortReverse() methods which are then used to influence the database cursor as to what rows to return.

This section of the documentation gives coding examples for use with ON FILL BUFFER.  You will end up with code similar to …


-- Scroll database cursor to fetch rows from given position
DECLARE c1 SCROLL CURSOR FOR  SELECT ... 
OPEN c1

  DISPLAY ARRAY arr TO scr.* ATTRIBUTES(COUNT=-1) -- Note use of -1 to indicate don't kndow total rows
    ON FILL BUFFER
       CALL arr.clear()
       LET ofs = fgl_dialog_getBufferStart() -- The row of the database cursor to start from
       LET len = fgl_dialog_getBufferLength() --The number of rows to fetch
       LET row = ofs
       FOR i=1 TO len
          FETCH ABSOLUTE row c1 INTO arr[i].*
          IF sqlca.sqlcode==NOTFOUND THEN
            EXIT FOR
          END IF
          LET row = row + 1
       END FOR

Nothing in life is free, so what is the cost of using ON FILL BUFFER ?  Some of the things to be aware of include …

  • It is no longer possible to automatically do anything where you would need all the rows.  This includes the automatic sorting via clicking on the column header, and the automatic calculation of aggregates when AGGREGATETYPE !=PROGRAM.  The 4gl array variable only has the visible rows.  If you want sorting then you need to use ON SORT and pass the sort properties into the database cursor.  If you want aggregates you need to use  AGGREGATETYPE=PROGRAM and calculate the total yourself.
  • There may also be a performance hit as you scroll up and down or resize the visible array.  Using ON FILL BUFFER, when the user makes new rows visible, some processing, typically database activity will need to occur to fetch these new rows of data, place them in the array, and send them to the front-end. Without ON FILL BUFFER, this same processing occurs before the array is visible and is normally not noticed  above the cost of the initial display of the window.
  • You cannot use multiple row select in conjunction with ON FILL BUFFER.
  • If using TREE, you need to use ON EXPAND / ON COLLAPSE to fetch additional rows of data.

We don’t often talk about efficiency within Genero development.  There are times where the additional cost of coding the ON FILL BUFFER can mean that your Genero application is kinder on its operating environment.  If you look at Google or any other search engine, note how the search only returns a screen full of data.  You could argue that these search engines and where you see similar sorts of search results are doing the equivalent of ON FILL BUFFER!  They are only fetching a screens worth of data at a time.  Note also how the same limitations I mentioned above apply to Google search results.