Hello,
We need to export data from a database based on user-selected filters into an Excel sheet for reporting. Currently, our process queries data in batches of 500 rows and uses the deprecated "Export CDT to Excel" function to generate the report, as we need to export 80 columns. Since we are running in batches, we cannot keep the smart service into separate start process as we are appending the generated document with new values and incrementing the start index.
However, as the data volume has increased, users are exporting data upto 50,000 rows, leading to memory heap issues causing Appian server to go down. The latest export functions have a limitation of 50 columns, which does not meet our requirements. We made process variables as hidden but still memory is hitting upto 6k AMU.
We are using mySQL as database which is hosted on AWS RDS. Currently we are using a!executeStoredProcedureForQuery() to query the data from database. Before we were using a view to query the data which caused db timeouts while querying. So we have created the store procedure and querying the data.
What would be the best approach to optimize performance while ensuring all 80 columns can be exported efficiently?
Discussion posts and replies are publicly visible
Hi Swathimdas Did you try using any temporary table approach? loading the in the temporary table with filtered data sets?
Hi Abhishek Karumuru Data is being queried from a view which consists of 13 tables and 3 views( which consists of nested views). If we use temporary table to query every batch and delete again, it will take more time and Appian has limitation of 10ms for querying data. Thats why we have switched on querying the view via store procedure. Even though, query time is reduced, since its running in batchsize of 500, its iterating 100 times to query all 50k records and generate the excel. Since we are using deprecated plugin, Export CDT To Excel, its taking time to generate excel which is causing memory heap issue.
Not a physical temporary table. But we have temporary table we can create for the that session when we querying a batch using Temporary table key word. Those will be automatically deleted when that session ends
Can you please elaborate on temporary tables? My main issue is because of the iterations as we are trying to generate excel while querying the data in batch of 500. Since we are running looping one instance 500 times, its taking time while generating excel as its appending the generated document.So, how can creating temporary tables solve the issue as we still have to query 500 times in the single session?