Huge data fetch - "Memory threshold" problem

...ted: [1,489])) (Data Outputs)

Please let me know the solution for this requirement to this "Memory threshold" problem

I'd highly recommend not creating an Excel document that contains one million rows.

One solution for your querying problem is to batch the query and run it multiple times. E.g. return 1000 rows, update document, return the next 1000 rows, update document, etc.

You can fetch this using a simple appian custom plugin using spring jdbc or even simple jdbc. You cannot handle this requirement using appian provided query tools i.e query entity , query rules because time for execution a appian databse operation is around 8 sec , you can change that setting in your custom.properties but that may have performance impacts on your environment. So i think you should create a simple appian plugin using java.

@saratt Afaik, the best possible way with the available functionality (in terms of OOTB and Shared Components) is making use of 'Export SQL data to Excel' smart service at http://bit.ly/2eZoZz6. Following are the benefits of this plugin:
1. No need for writing query entities and implement batching in the queries and process model
2. No need to retrieve huge amount of data and store it in PVs

So by making use of a native SQL statement (which may be sourced from a table or view or an on-the-fly SQL statement making joins) in the above-mentioned smart service, you could pull the data into Excel. In case if the smart service takes more time to execute and is listed under the category as mentioned at https://forum.appian.com/suite/help/16.3/Appian_Health_Check.html#slow-smart-services, then it might be worth implementing batching in the native SQL statement so that we can at least escape from using query rules which again involves Prepare, Execute, Transform phases and consumes time.

For something this large, I would look at the larger solution. If a million rows of data are needed in excel, what is the purpose, audience, and use. Based on those answers, you may find that including a BI tool or something else to augment your whole solution, might be the right solution.
Pulling that much data back into Appian will consume your resources and if not batched properly, exceed some of the limits (that are there to ensure that the system is not at risk due to an errant process).

Also, this does not sound like a good idea. Why Excel? Is this to export data? Excel can't handle that many rows and the file will be extremely large, csv can be used with a plugin but even then, the file is unviewable. If you are exporting the data to another DB, a csv may be appropriate but you may want to explore other technologies (ETL tools, etc.)

Friends, thanks to all of you for the responses. Each of the suggestions made a valuable contribution for my research on a POC to understand the pros and cons for handlig with huge data. - Sarat