a!queryEntity given 2000 records it's giving us this error

Question

Hi, we have a requirement to view/export report of more or less 3000 records with 90 fields. We tried using queryentity() and the exportreportfromprocessmodel function and it works with minimum datasets (ex. 100 rows) , but when we inserted 2000 records, we are getting an error on the queryentity. To give you an idea, the queryEntity is referencing a table view that left joins 10 normalized tables. From 40 secs loading time in My SQL, we have optimized it to load in just 7 secs, but when called using the function a!queryEntity given 2000 records it's giving us this error: "Expression Evaluation error in rule .... at function a!queryEntity". Is this a limitation in Appian? Please advise. 
 OriginalPostID-181807 
 OriginalPostID-181807

PhilB · Accepted Answer

Seven seconds still seems very slow for a view; is it a particularly large amount of data? Have you used EXPLAIN on the view's SELECT statement to check that it's using indexes correctly? If you're able to upload the SELECT statement then some of us may be able to optimise it further.

sikhivahans · Answer

@jessicae To the best of my knowledge, the settings 'conf.data.query.timeout' or 'conf.data.query.memory.limit' described at https://forum.appian.com/suite/help/7.9/Post-Install_Configurations.html#Query_Rule_Limits is not just about the query rule, but it is about any query made to database using query rule or query entity or query record.

And the 'Caused By' in logs is clearly indicating that the threshold value is reached which means that you need to get the data in batches rather than fetching them all at a time.

As of now, the issue clearly seems to be about memory threshold, but it is also good to focus on the View's performance, because 7 seconds is much slow for a view as said by Phil. When queries are made in Appian using such views, it might result you in time out at times as the Prepare, Execute and Transform phases levies some more burden on Appian while getting the results from database into Appian.

Probably what you should be doing now is:
1. Query the data in batches.
2. Improve the performance of View as much as possible if Phil provides some insight into the improvements of the sql script uploaded by you.