Is there any more info available on how Paginginfo works within Appian ?

I've an upcoming use case where we will have a source of truth table which over time will become quite large. We will be doing all the user tricks to keep it responsive but at some point we will be querying it and no matter how many filters etc we get returned the data subset will be for a large amount of records. (let pretend 10,000).

i'll use pagingInfo to control the grid, but whilst I'm displaying records 1 to 100, what is happening to the rest of the data set ? I'm assuming it's floating around in Appian memory somewhere. 

I'm interested in people's thoughts here ?

  Discussion posts and replies are publicly visible

  • Hi,
    It really depends whether you are querying the truth table inside a load() or with()
    if you query it inside a load() with negative batchsize and then use the data in the paging grid with a separate pagingInfo having smaller batchsize (say 20) then all the data will be available in the queried datasubset however if you query it inside a with(), everytime you navigate to the next/previous page a fresh query gets fired and fetches the result as per the start index and batch size.
    I hope this information helps.
  • Depends on how you are querying - using load() or with(). As mentioned in one of the comment with() will trigger a fresh query each time you navigate. Have you tried making your batch size as dynamic for such large data being fetched. Assuming you are using a stored procedure as well - gives you more flexibility in controlling what you want
  • Hi Paul,
    I have similar use case as you mentioned. If we define start index and batch size correctly as guys already responded, Appian will fetch only that much of data. E.g. if start index is 1 and batch size is 100, Appian will holds memory of datasubset with 100 records. Along with this, it will have some metadata information e.g. totalCount but nothing much. With properly defined paging, it doesn't matter how many records are in database. its just your batch size will define how much Appian memory will be utilized. Also we need to make sure to use CDT with precise number of fields which are required for that screen. Extra unused fields will increases payloads and memory unnecessarily.
  • I think you should try this recipe first

    It will give an idea, You should use with() function to get data in subset with your query - better use queryentity and not queryrule.

    If you use a view with proper indexing of columns it should work perfectly fine.
  • We routinely retrieve small subsets of data into a paging grid that ultimately comes from a much larger dataset. If you use a with() wrapped around your rule to retrieve data, and display your datasubset values somewhere while testing, this behavior becomes more clear.

    For example, your total dataset contains 500 items, but you only want to display (and keep in memory) 100 at a time. You set your paging info values to: startIndex:1, batchSize:100.

    You display your grid, if you display your datasubset in a paragraph field, you'll see three important values:
    datasubset.startIndex:1, datasubset.batchSize:100, datasubset.totalCount: 500,

    If you page your grid to the next one hundred and examine your values, you'll see:
    datasubset.startIndex:101, datasubset.batchSize:100, datasubset.totalCount:500

    In other words, it will retrieve values from 101 - 200 by taking the startIndex as the starting point and the batchSize as the amount to retrieve in this batch

    It is imperative that you use the value and saveInto of your pagingGrid to update the values used to retrieve the data from the database (or other datasource)

    From a performance perspective, if you are using a database backend and are retrieving these values from a table, consider the performance possibilities of Indexes, Partitions and even periodically materialized views to speed retrieval
  • Richard, It is very clear and to the point.  Thank you!  For some reason on my page, the paging grid is behaving bizarre!  I am retrieving the grid data in a WITH clause and the paging info is a local variable in LOAD.  The grid data changes based on changing user-selected filter values so the WITH is working as expected but the TOTAL records retrieved is always equal to the BATCH SIZE.  If I don't specify a page size then the page size defaults to 10 otherwise, it defaults to whatever I specify the page size to be.  I thought the batch size would determine the page size as we go through the pagination.  What am I missing?  I am using 19.4.