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

Hi Jessica, I think you can try retrieving the data in batches of 1000 because of Appian's limitation. You might find the below post useful
forum.appian.com/.../e-154621

@jessicae I believe that the performance of the query entity not only depends on the database object but it also depends on how you are utilising the Appian features. For instance, you may check 'Slow data store operations' section at https://forum.appian.com/suite/help/7.11/Appian_Health_Check.html which speaks about the various phases in an operation etc. So simply speaking, definitely you could see a good difference between the execution times of native SQL query and Appian query execution times. And anyhow with respect to limits, Appian has already specified them in terms of execution time and amount of data at https://forum.appian.com/suite/help/7.9/Post-Install_Configurations.html#Query_Rule_Limits. Appian practitioners here could throw more light on your question provided if you could post the 'Caused By:' reasons from the stack trace when you are experiencing the issue.

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.

thanks @sikhivanans @philb , we are not using query rule due to the 1mb limit, I believe using queryentity would be able to load more data, is it a limitation in Appian for displaying/exporting big data? As mentioned above, it is only 2000 records but with 90 fields and coming from 10 tables. I'm not sure if this a limit in Appian or maybe an issue on our View. I'm attaching the select code, so you could advise as well. Will post the error log in a while

VIEW_allTables v4.sql

error log: Caused by: com.appiancorp.suiteapi.common.exceptions.AppianRuntimeException: com.appiancorp.suiteapi.common.exceptions.AppianException: Memory threshold reached during output conversion (rule: [queryentity expression], type: [SUNRISEVIEWTRBLSHTDETAILSDT39458], threshold: [1,048,576 bytes], objects successfully converted: [1,073]) (APNX-1-4164-024) 04:35:42,310 INFO [stdout] (ajp-/0.0.0.0:8009-40) at com.appiancorp.type.external.teneoimpl.QueryRuleExceptionHandler.handle(QueryRuleExceptionHandler.java:50)

@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.

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.