Batch size in query Entity

Question

Hello, 
 We are facing a wired issue. We are having a query entity with selection only, when we test it with batch size 50 it takes more time as compared when we use it with -1 batch size. Can anyone explain why? and how batch size query. 
 
 Thanks & Regards 
 Sahil Batra

Robert Shankin · Accepted Answer

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: 
 
 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. 
 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.

lucasj · Answer

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.

Batch size in query Entity

Top Replies