Huge data fetch - "Memory threshold" problem

saratt over 7 years ago

Hi All,

I have a requirement in which there is a table of 30 columns and 1 million records.
Requirement is to fetch those records and write the data into an excel document.

1. I have created an xsd/cdt which got verfied and mapped with the table in DB
2. When i am trying to query the data with a query rule, the rule is fetching data only if the records number is <= 1400

If i try to fetch 1500 records, error occurs on the script node in which i am fetching and it sends me following error in the alert

There is a problem with task “Script Task” in the process “XXX Test”. ERROR:An error occurred while evaluating expression: CCC_CDTs:rule!YYY(pv!cid) (Expression evaluation error in rule 'YYY' at function 'queryAAA': Error evaluating function 'queryAAA' :

Memory threshold reached during output conversion

(rule: [YYY], type: [CCCDT28858], threshold: [1,048,576 bytes], objects successfully conver...

Discussion posts and replies are publicly visible

Top Replies

sparshs over 7 years ago +1

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…

Parents

0 sikhivahans over 7 years ago

@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.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Reply

0 sikhivahans over 7 years ago

@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.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Children

No Data