a!queryEntity: An error occurred while retrieving the data

Bhargavi,

A few follow up questions/suggestions:
1. Have you checked the application server logs? Is there any additional information in the logs that gives you more insight into the issue? There should be a corresponding log entry if you're getting the error in Appian.
2. Have you tried running the query entity from a separate expression rule? If so, can you make the batchSize smaller to eliminate your question around the data volume?

Hi bhargavip0001 Yes, generally you may get this error(An error occurred while retrieving the data) when a query entity takes more time than expected while fetching the data from the Database.

As per my understanding, please find my suggestions mentioned below:

• We cannot call 5k rows as a huge data for reporting, as there are some other factors as well
  
  • Number of columns for each row of the corresponding view
  • Number of characters exist inside a column of a particular row
• Yes, when your view itself is taking 3 or more than 3 seconds of time to load, then i believe it's a serious concern in terms of performance as this will cause your Appian rules to break and you can expect the same error which you are currently getting i.e. An error occurred while retrieving the data

Now I have couple of questions to understand the actual issue:

• Does this view frequently takes approx 3 seconds of time load from DB Side?
• Did you try executing any other view which has less amount of data while compared to your current view, if so was that slow as well?
  • The reason why i am asking you this question because, sometime if there are any SQL session are active and performing some heavy operations on DB, in such case any DB operations performed over the Database may take more time than expected.
• Also, did you try executing the same View with same amount of data in some other environment like Test?

Possible Resolution for this issue:

• Try to switch towards Materialized view instead of operating over a View because of the following reasons:

  • Views are not stored physically on the disk and hence this can be defined as a virtual table created as a result of the query expression. However, Materialized View is a physical copy of the base table.

    In other words:

    Materialized Views are disk based and are updated periodically based upon the query definition, whereas Views are virtual only and run the query definition each time they are accessed.

  • And this can put a major impact on the query performance while operating over huge amount of the data.
• However, if you still want to continue with the View, then i would recommend try having a look on your Join Queries and use the appropriate joins to improve the performance
• Also if possible, try upgrading/customizing your UI design by adding some filters on your SAIL UI for the report containing the time periods/duration for which a user wants to see the report, let's say
  • Last week (Default)
  • Last Month
  • Last quarter
  • Last 6 months
  • Last Year
  • Custom (between two data range)
• In this way, you can avoid querying the whole set of data during every time this Interface loads, even when user is only interested in viewing the report over some period but not all.

Hope this will give you some idea in resolving this issue and building a better solution.

Hi Bhargavi, generally we used to get this error(An error occurred while retrieving the data) when a query entity takes more time than expected while fetching the data from the Database.

Please try to apply more filters on UI to fetch minimal amount of data from DB along with batch size.

Hi Bhargavi,

The Error while retrieving data is general one which can be displayed in time out scenario or the data exceeded more than 1 mb.
Did you get a chance to look into server logs? it will give you more details on it.

Also if you expect More thousands of rows in future you can think about the option given by Alok.

This problem has been addressed in my other post.

community.appian.com/.../71096