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.
Thanks for following up — let me clarify the use case in more detail:
I’m working with two tables:
Tickets table – around 700,000 rows
Tasks table – over 8 million rows
There’s a one-to-many relationship between them: each ticket can have multiple tasks. A single employee might be assigned to the same ticket multiple times through different tasks.
I need to retrieve all tickets that a specific employee has been involved with, based on their task assignments.
So while an employee might not have 100,000 active tasks in the usual “to-do” sense, the join between these tables can result in very large result sets, especially for employees who have worked on many tickets over time.
Normally, I would definitely use pagination. But in this case, business users want to see and analyze the full list of tickets for an employee — it’s more of a reporting/auditing/Follow-up-on-tickets scenario than a task management one. They often export this data or perform cross-referencing, so partial views aren’t helpful.
forEach
Totally fair point — I know they’re not ideal for large datasets. I’ve been using them to break down the queries into 5,000-row chunks (which is Appian’s record limit per call), just to avoid timeouts. But I agree this isn’t a sustainable or clean long-term solution.
Mohammed Dyab said:business users want to see and analyze the full list of tickets
... to do what? I suggest to discuss that first, then build something that supports achieving these goals.
You're right — it's important to understand the purpose behind showing the full list of tickets.
I’m working in the banking sector, where business users — especially branch employees and support teams — need access to all historical tickets related to a customer. This isn’t just for reporting; it's about being able to quickly search and confirm customer interactions, even from a year or two ago.
For example, if a customer walks into a branch today and refers to a service request or issue they submitted one or two years ago, the employee needs to be able to:
Confirm the customer’s previous tickets or service orders
Check the ticket history to understand what actions were taken
Provide immediate assistance based on full context
Limiting the data to just recent or paginated results could delay service or lead to incomplete information, which affects customer satisfaction and trust.
So, the goal is to ensure that employees have access to the full ticket history on demand, in a fast and reliable way — not for processing, but for searching, confirming, and following up.
I hope this gives better context to the need for full data visibility.
Sure, but do you want to but 100k rows of data on a screen and then make the clerk search this with his pure eyes?
A clever made search capability could be a so much better approach.
Of course, the search won't be through the eyes. There are several filters on the screen, such as ticket status, date, customer name and number, etc. However, the screen will display data through a record or query.
What is the best way to get data from the database to Appian.