Batch size in query Entity

Hi sahilb

I think it might be due to size of the RAM in server.

Thanks,
ravalik

It might a temporary memory issue . Are you facing this issue repeatedly ?

When adding a batch size other than -1, Appian converts this to a LIMIT clause when executing the query.  Since the LIMIT clause takes some time to execute, for smaller datasets, this can take longer than executing the same query without the LIMIT Clause. 

For example, using batchSize of 50, the query might look like:

SELECT
    ID
FROM ORDER
LIMIT 50;

And with a batchSize of -1, it would be:

SELECT
    ID
FROM ORDER

 

Obviously this won't hold true as your dataset grows, but for queries that only return a small number of rows, sometimes -1 is faster.

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.

lucasj

What you're not accounting for in your response is the count query that precedes the actual fetch.

Thank you for your reply, It is helpful to understand the real function of batch Size.
Thanks lucasj