Appian Community
Site
Search
Sign In/Register
Site
Search
User
DISCUSS
LEARN
SUCCESS
SUPPORT
Documentation
AppMarket
More
Cancel
I'm looking for ...
State
Not Answered
Replies
7 replies
Subscribers
7 subscribers
Views
5568 views
Users
0 members are here
Share
More
Cancel
Related Discussions
Home
»
Discussions
»
General
batching the query
rohandhondilalk401
over 7 years ago
Hi,
I want to know how does exactly batching the query will work in Appian in retrieve the larger data ?
OriginalPostID-256157
Discussion posts and replies are publicly visible
0
aloks0189
Certified Lead Developer
over 7 years ago
@rohandhondilalk401 As per my understanding, Appian internally uses JPA in order to perform CRUD operation on DB, when we work with database we may come across though 2 scenarios where we may need to go for Batching
1. While retrieving large amount of Data: As JPA internally uses JDBC API, so max number of rows for retrieval of data is around 10,000,00 (10 Lakhs, it may exceed if the Row contains less amount of data with respect to column value for each row retrieval), while fetching that large amount of data the time consumption will be more and hence we may get some database related errors, so instead of fetching huge amount of data at a time, we can configure the query to fetch limited set of rows to be retrieve one after another, such requirement we can achieve by using batching
2. While storing similar kind of set of objects concurrently: here in order to reduce the number of hits on DB, we can add all the similar kind of objects into Batch and can deliver it to database engine, which will take care of inserting the data into the table, in the same order how it was inserted into the batch (While storing an array of objects in Appian)
=> the major factor in batch is: Batch size & the Total number of rows to be retrieved, where batch size helps to increase the row count, i mean
if first retrieval at Batch Size 0 is : 1 to 10
then second for Batch Size 1 could be: 11 to 20 and so on
hope this will help you
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
rohandhondilalk401
over 7 years ago
Hi Alok, Thanks for the detailed information. But how batching wil help Query rule to fetch data ? Can you explain with an example if you are ok means?
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
Sachin
A Score Level 1
over 7 years ago
Go through this link if haven't about batching and data base performance best practices.
forum.appian.com/.../Database_Performance_Best_Practices.html
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
Rama Thummala
Certified Lead Developer
over 7 years ago
@rohandhondilalk,
Adding to the above, the limit would be based on the size of the memory the records take and time taken to retrieve , not based on the number of records we retrieve.
The default value is 10MB and time limit is 10 seconds.
These values can be configured.
conf.data.query.timeout=10 and
conf.data.query.memory.limit=1048576
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
sikhivahans
over 7 years ago
@rohandhondilalk Let's say you would like to retrieve 'x' records at a time. Batch size will be always equal to 'x' and is fixed. Whereas the start index varies across each batch as 1 for the first batch, 1+x for the second batch, 1+(2x) for the third batch, 1+(3x) for the fourth batch and so on. A fixed batch size, a varying start index across each batch, and fn!ceiling(totalCount of query rule or entity/x) number of batches(or iterations) should help you accomplish retrieving the records in batches.
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
Sally Mason
Appian Employee
over 7 years ago
Be mindful that if you increase the default "conf.data.query.timeout" and"query.memory.limit", you increase the risk of having long running queries that Application Users perceive the Application to be 'frozen' or broken... Please look at redesigning or optimizing your query before changing any settings, and note that for Appian Cloud, these settings are locked down/not configurable.
I've seen many cases where people attempt to "solve" the these kind of issues by increasing values.. which is way of compensating for poorly designed queries or exceeding the reasonable limit of how much data can be returned - remember the impact on of the End User experience - its best to optimize what you can and have fast loading Records and Tasks..
The best and recommended approach is to only bring back what is actually needed - or only what is displayed use batching/pagination to your advantage. If you are finding the database query is very slow/not completing, an alternative approach is to create a "view" at the database level to reduce the returned results.
For best Appian database practices, please see
forum.appian.com/.../Database_Performance_Best_Practices.html
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
rohandhondilalk401
over 7 years ago
Hi Every one , thanks for the deep explanation !!
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel