Batch size in query Entity

Hi Sahil - 

A query batch size helps Appian both retrieve and display large data sets in an efficient way by allowing designers to design UIs and components to retrieve a subset of data while showing a count of the full data set.

For example, when the batch size is set to 100, indicating the designer wants to first use or show the top 100 results, Appian first retrieves the total count of the target database entity. This allows the application to retrieve the first batch only (in this example, the top 100 rows), and to then indicate to the end user or system how many more results exist.

When batch size is set to -1, the effective batch size is unlimited. There is no preliminary count query when batch size is unlimited, (and hence the reduced latency, i.e. it's faster).

The risks associated with this setting are as follows:

1. Less control over query response size: Without a specified limit on the number of rows returned, the response size could be large in the case of query parameters that return more than one row. As a platform guardrail, by default Appian will throw an error to end user anytime query response exceeds 1MB.
   
   
2. Potential for unexpected behavior: If the query returns more records than expected, a dependent interface expecting a limited result set that contains this unbounded query might not behave as expected.  When receiving the unbounded number of rows in a query response, the user interface or other application component needs to handle the results appropriately.

Use of an unlimited batch size is discouraged in situations where the length of the response is unknown, as it increases the likelihood of risk #1 mentioned above. 

Hi Sahil - 

A query batch size helps Appian both retrieve and display large data sets in an efficient way by allowing designers to design UIs and components to retrieve a subset of data while showing a count of the full data set.

For example, when the batch size is set to 100, indicating the designer wants to first use or show the top 100 results, Appian first retrieves the total count of the target database entity. This allows the application to retrieve the first batch only (in this example, the top 100 rows), and to then indicate to the end user or system how many more results exist.

When batch size is set to -1, the effective batch size is unlimited. There is no preliminary count query when batch size is unlimited, (and hence the reduced latency, i.e. it's faster).

The risks associated with this setting are as follows:

1. Less control over query response size: Without a specified limit on the number of rows returned, the response size could be large in the case of query parameters that return more than one row. As a platform guardrail, by default Appian will throw an error to end user anytime query response exceeds 1MB.
   
   
2. Potential for unexpected behavior: If the query returns more records than expected, a dependent interface expecting a limited result set that contains this unbounded query might not behave as expected.  When receiving the unbounded number of rows in a query response, the user interface or other application component needs to handle the results appropriately.

Use of an unlimited batch size is discouraged in situations where the length of the response is unknown, as it increases the likelihood of risk #1 mentioned above. 

Hey Roy, what will be the efficient way to retrieve all data though query, query fails, when there is large number of data, so when data is too large, then how should we handle the query, so that, it do not give any exceed limit out errors.

One option might be to test beforehand by collecting totalCount only, and if that doesn't seem unreasonable pull the entire dataset.

Hi lokeshk

Generally speaking, and according to current product behavior, I wouldn't recommend using a -1 batch size in your query entities.
The only exception would be if the data store entity itself is constructed in a way to limit the number of rows returned.
For example, you could create a view that ALWAYS returns at most 5 rows.
If you have scientific certainty of what will be the size of the result set, and that size isn't an overflow/timeout risk, then you can probably expect not to have problems with related functionality in your application.

Paging info is, in part, a guard rail against the undesirable behavior you described in your last post.

Hi Davidl, What ever the scenario, i will recommend pulling batches only when the data grows largely. At initial development time it seems batchSize=-1 might be pulling data in less time compared to batchSize:50. If current development was done based on batchSize -1 and later point of time if you found that pulling all the records causing default 10ms time out issue or 1mb issue, we need to redo batchSize 50 or less. What i would suggest is have rule input for paging and based on null check do retrieve with batchSize -1 else with user required bacthSize.

Hello Rob,

Thank you very much for your reply, It is very helpful and clear all my questions related to batchSize.
Thanks Robert Shankin Thanks
Sahil

we have some conditions met with logical expressions, but still the data is too large that query fails to fetch all data, but if i reduce the batchsize then it works, nut according to my query condition i need all data, but it fails to fetch due to time limit?

So what should we do ideallly

Hi All,

Just to add to the above query, we faced an issue with the sort parameter wherein if we mention the sort in paging info, the amount of time taken to retrieve the data was more if the back end data count was high.

My understanding is if the data set in the db is high say 1000s and if we try to get only say 10 records by applying a sort it takes more time when compared to no sort as the query will run on the whole set. Any suggestions on how this scenario can be better implemented.

Thanks.
Hitesh

Hello Lokesh,
I will say that if possible do that logical things in the DB that will be good.

Robert Shankin or any other approach we can follow for this situation. Can you please guide us because I am also having this problem .

Thanks
Sahil