Question

Memory Threshold Issue

Forum|Forum|5 years ago
June 28, 2021
20 replies
0 views

Hi,

We are facing the below error while trying to execute the expression rule to fetch data from data base table:

An error occurred while evaluating expression: listofIDtodelete:rule!abc(cons!xyz) (Expression evaluation error in rule 'abc' at function a!queryEntity [line 5]: An error occurred while retrieving the data. Details: Memory threshold reached during output conversion (rule: [queryentity expression], type: [DataReportDT7042], threshold: [1,048,576 bytes], objects successfully converted: [131,071])) (Data Outputs)

Please provide your help regarding the resolution of this issue.

stefanhelzle0001

The only sustainable solution is to refactor your design. Think of batching, looping etc.

A

ayushimAuthor

Thanks Stefan for your prompt response.

But we are using this data for reporting purpose. The aggregation functions are applied on the whole data so how can we apply batching logic in it.

stefanhelzle0001

Then you need to optimize your query. Do your really need all data returned? Is this about reporting into Excel or charts?

davel001150

One possibility might be Execute Stored Procedure. That can also be called upon to run a database FUNCTION, which can return simply the results of your aggregation. I assume you just want things like COUNT, AVE, MIN, MAX. You can have the DB run all the aggregation calculations and have it return only the few integers you need, if what you need is a few integers. That way, you don't have to bring the whole dataset to bear for Appian to perform aggregation, but let the DB do it.

mikes0011

Brainy

Considering that you seem to be fetching "listofIDtodelete" - how many columns are you returning in your query?

A

ayushimAuthor

We are fetching only a single column (ID) of the database table but there are over 2 Lakh rows.

mikes0011

Brainy

Then I defer to [mention:126a676c85024855948336691b0a7b92:e9ed411860ed4f2ba0265705b8793d05]'s original suggestion of employing Batching somehow within your process.

peter.lewis

Employee

I think we need to take a step back - what is your use case? There might be a completely different query you could run depending on what you're trying to do. How are you using this data? What is the goal?

A

ayushimAuthor

Hi Peter,

Using expression rule, We are fetching all the IDs from the database table and passing it to script task in the process model as the 'IDs to be deleted'.

While we are executing this process model, it is failing on this script task and throwing the above error.

stefanhelzle0001

Is this part of a larger DB cleanup process? Will it run periodically? Why is there so much to clean up? Could it be prevented?

Is the query to fetch the IDs a simple one? Like "select id from table where tobedeleted=1"? If yes, did you consider to use the Query Database Node and a statement like "delete from table where tobedeleted=1". There is some drawbacks going that way, but it might be worth it.

Sign up

Login with SSO

Login to the community

Login with SSO