Pagination - Memory threshold issue.

Are you using gridField to achieve the above mentioned grid in the attachment?

@rameshg The component being shown above is a Forms Designer component as mentioned at forum.appian.com/.../Forms_Designer_Components.html whereas gridField belongs to SAIL.

@saicharanb As per my observation so far, you have been working on the Forms Designer and Paging Grid Component in particular. I believe you are trying to target the performance improvement and also trying to know how the pagingInfo works:

How it works:
1. When it comes to Paging Grid Component that belongs to Forms Designer, data!pagingInfo is the property that takes care of pagination and documentation at https://forum.appian.com/suite/help/16.2/Forms_Designer_Components.html#executing-the-data-set-expression might help you understand this.
2. When it comes to pagingInfo, the startIndex will be 1 initially. Batch size parameter will be controlled by 'Rows per page' attribute of the Paging Grid Forms Designer Component. And with regards to sort, the configuration in the query(or target) rule should take care of this initially(You may also change this default sort behavior by making use of the approach as mentioned at https://forum.appian.com/suite/help/16.2/Forms_Designer_Components.html#specifying-a-default-sort-column). Now the data!pagingInfo keeps track of all the changes in sort and startIndex which we achieve in SAIL by updating the values of Paging Info variable in the 'saveInto' of gridField.

How to overcome the performance issue:
1. Get all the data in the process. Use the BEST practices(batching the data, looping the process in case of large amounts of data) to retrieve data so that this won't hamper the performance, let's say the entire data is stored in a variable called 'queriedData'. While configuring the Paging Grid on the form, use the todatasubset(pv!queriedData,data!pagingInfo). The only difference we are trying to make here is, instead of trying to pull the entire data in the form, we are pulling the data in the process before we hit the form, that too, in the best possible way which thereby overcomes the performance issues and an improved user experience is the additional benefit.

Why retrive all the data in the process:
I believe one might get a doubt as to why we are ending up in retrieving all the data in the process. This is because, in the SAIL, we do have an ability to store the pagingInfo in a variable, update it in 'saveInto' and provide it to the data sorurce rule every time and store fresh values in the data subset variable which could happen all the time when we use with() and on demand when we use load(). As we don't have this kind of mechanism in the Forms Designer(as far as I remember, there isn't any) we use data!pagingInfo which will take care of all the aspects whose startIndex, batchSize, sortInfo will be derived as I have explained earlier. That's why we query the data beforehand in the process so that we don't end up querying a huge amount of data in the form which causes performance issues and a bad user experience. Please note that this works well only for large amounts of data sets and when the data set is small, querying in the form should be fine.

In case if you have any issues in implementation, I would suggest expanding as much as you can on the objects, that is, the definition of the rule, the configuration in Process Model without which giving suggestions might become difficult.

Also, I would like to comment on the above solutions/attempts made:

Usage of a!pagingInfo(startIndex:1,batchSize:-1): This approach fails miserably when the data set is huge and the performance improvement you are trying to target will not be achieved in any case.

Usage of a!pagingInfo(startIndex: 1,batchSize: 10) as in rule!WFT_tempoSearchQueryPagination(pv!process,pv!subprocess,pv!action,pv!requestorName,pv!company,pv!reference,pv!sapRefNo,pv!fromDate,pv!toDate,pv!monetaryValue,pv!priority,pv!wftRequestID,pv!salesOffice,pv!requestStatus,a!pagingInfo(startIndex: 1,batchSize: 10),loggedinuser()):
This won't work and answer could be driven from this question - What will let Appian know how to update the pagingInfo and provide the same to expression rule to refresh the data set? In the modern SAIL interfaces, this happens by pushing the data into variables and refresh on demand or assign values and refresh everytime when using with(). But where is this mechanism in the traditional Forms Designer's Paging Grid Component? (The data!pagingInfo is the only way through which the pagingInfo can be driven and this couldn't be configured to achieve pagination.)

Also, afaik, the comments made above will help you definitely when you are using gridField in modern SAIL but might not when you are making use of Paging Grid Component in traditional Forms Designer.

I agree with Sikhi's comment for the use of batchsize:-1.
@Sikhi I have a doubt in what you said to overcome the performance issue.
In the forms designer when we query the data beforehand in the process, this means do we want to have it in a Process Variables. If so will that be good to have huge amount of data in the Process variables? what if many instances are running in this case?

@chandrasekharg Querying the entire data in the process is the only way I am aware of. Until and unless I miss something, there isn't any other way wherein we can query in batches on the form instead of pulling the entire data into process variables and batching the data in the process variable.

If so will that be good to have huge amount of data in the Process variables? what if many instances are running in this case?
Definitely, it's not a great idea to store a huge amount of data in the process variables and obviously it has some adverse impacts when there are many instances running in the system that is designed on a similar pattern. Maybe we can go for alternatives such as follows:

Option 1:
a. Have a drop down that shows paging info(such as 1-10,11-20 etc) and auto submit the form upon changing the value in the dropdown and use javascript.
b. You can use the Editable Grid to get rid of the pagination and sorting controls as we use the custom pagination logic now.
c. Post form submission, query the data in the process depending on the paging info we get from the dropdown in the form.

Option 2:
a. Leave the paging grid as is and show a radio button/drop-down which shows the options such as Get first 100 records, Get first 200 records or something in a similar way.
b. Again post form submission query the data based on how you design the batching.

In case if we query the entire dataset and store it in the process variable, then it should be done by querying in the best possible way followed by archiving the instance immediately upon completion of the task. That way we can at least control few things that are in our hand. It's just better when compared to querying the entire data set at a time which hampers the performance and also frustrates the end user by increasing the loading time.

@sikhivahans: Thank you for the information/ explanation. Now in my scenario, is fetching the entrire data using batchSize=-1 only option to increase the performance and avoid memory threshold error ?

@saicharanb No, batchSize = -1 while querying leads to memory threshold errors or performance issues. So prior to hitting the task in the flow, get all the required data and store it in a variable. And the way you get the data should be in form of batching. That is let's say you have 1000 records. Instead of giving a batchSize of -1 and querying at once, perform multiple queries. Let's choose an arbitrary number which doesn't effect the performance, maybe 100. Now you should be performing multiple queries as follows: rule!WFT_tempoSearchQueryPagination(a!pagingInfo(startIndex: 1,batchSize: 100)), rule!WFT_tempoSearchQueryPagination(a!pagingInfo(startIndex: 101,batchSize: 100)), rule!WFT_tempoSearchQueryPagination(a!pagingInfo(startIndex: 201,batchSize: 100)) and so on till rule!WFT_tempoSearchQueryPagination(a!pagingInfo(startIndex: 901,batchSize: 100)). This way, we get small amounts of data rather than trying to get entire data set in one shot which hampers the performance. Store all the data made from these queries in a PV and provide the same to the Paging Grid in the Forms Designer form which would look like todatasubset(pv!queriedData,data!pagingInfo) where the pv!queriedData is the variable that stores the results of the queries made as discussed above.

@sikhivahans: I am using page grid not SAIL form. May be that's the reason batch size is not dynamically updated. Is there any way to dynamically update pagingInfo with the next batch size? For example: I have 30 records to fetch, batch size is 20, only 20 records appear in the results. Other 10 records 21-30 are not fetched. Can we update the batch size once initial set of records are retrieved?

=rule!WFT_tempoSearchQueryPagination(pv!process,pv!subprocess,pv!action,pv!requestorName,pv!company,pv!reference,pv!sapRefNo,pv!fromDate,pv!toDate,pv!monetaryValue,pv!priority,pv!wftRequestID,pv!salesOffice,pv!requestStatus,data!pagingInfo)

data!pagingInfo can solve my problem? I read that pagingInfo is based on the rows per page in Page Grid. If I reduce "rows per page" and use pagingInfo in query rule.
Thanks in advance.

@saicharanb Yes, give a value in the 'Rows per page' as desired by you. And make use of pagingInfo as mentioned below:

=rule!WFT_tempoSearchQueryPagination(pv!process,pv!subprocess,pv!action,pv!requestorName,pv!company,pv!reference,pv!sapRefNo,pv!fromDate,pv!toDate,pv!monetaryValue,pv!priority,pv!wftRequestID,pv!salesOffice,pv!requestStatus,data!pagingInfo,loggedinuser())

Let me know how it went.