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

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

  Discussion posts and replies are publicly visible

Parents
  • The SQL looks ok to me, other than a few ambiguous field names (eg WHEN requesttype = ... should probably be WHEN master.requesttype =...) and it looks like you're hitting the memory threshold anyway; optimising the SQL won't help with that. As Sikhi says, you'll need to query the data in batches.

    That said, I'd be interested to know how long the SELECT statement takes to run when run directly on the server and without inputs rather than by simply rendering the view, as 90 fields is rather a lot... Also it might be worth running EXPLAIN against the SELECT statement to make sure that each of the left joins is using a key of some sort.
Reply
  • The SQL looks ok to me, other than a few ambiguous field names (eg WHEN requesttype = ... should probably be WHEN master.requesttype =...) and it looks like you're hitting the memory threshold anyway; optimising the SQL won't help with that. As Sikhi says, you'll need to query the data in batches.

    That said, I'd be interested to know how long the SELECT statement takes to run when run directly on the server and without inputs rather than by simply rendering the view, as 90 fields is rather a lot... Also it might be worth running EXPLAIN against the SELECT statement to make sure that each of the left joins is using a key of some sort.
Children
No Data