Hi,
What is the most efficient way to retrieve data from a large table (more than 4 million rows) in Appian?
When I use queryEntity to fetch data from this table, it takes a long time to execute, which negatively impacts Appian’s performance. How can I improve the data retrieval process?
queryEntity
My current approach to solve the issue:
I split the data retrieval into smaller batches. Specifically, I:
Created multiple records that point to the same source table.
Used the foreach function to query each record in batches of 5,000 rows.
foreach
Used the union function to combine the results from each batch.
union
Used the union function to combine the results from each query record.
Then, combined the results across all the records to get the complete dataset.
I’m concerned about future system complexity.Do I really need to create multiple records and change the system design just to retrieve all the data from a large table? Or is there a better, more efficient way to handle large data retrieval in Appian without adding complexity?
Thanks,
Discussion posts and replies are publicly visible
What are you trying to accomplish? In pretty much every use case you should be paging through the data.
Thanks for the response!
I completely agree that in most scenarios, paging is the ideal solution — especially for improving performance and user experience.
To clarify, the goal here is to display a full task list for certain users who have over 100,000 tasks assigned to them. Due to business requirements, we need to load all of these tasks into a table for review and reporting purposes, not just page through a limited subset.
When I try to use queryEntity to retrieve all tasks in one go, it becomes very slow and degrades system performance. To work around this, I’ve been splitting the queries into multiple 5,000-row batches using foreach, and combining results using union. This works, but I’m worried it’s not scalable and adds a lot of complexity — especially since it involves creating multiple records pointing to the same table.
So my question is: Is there a better way to retrieve large datasets in Appian (like 100k+ rows) efficiently, without redesigning the system or creating multiple records?
Would appreciate any suggestions — maybe something database-side or a smarter way to load/display the data?
Thanks again!
1. How can a single person have 100K tasks?
2. Why can't you paginate the data?
3. Why are you using forEach (should never use that on big datasets)
4. Same goes for union.
If you need to crunch numbers and report on this data, I would suggest materialized views if performance is a concern. But if its just listing the tasks, there's not much more you can do.
To be honest, this feels more like a business problem than a technical problem.
You could just connect something like PowerBI to that database.