a!queryEntity: An error occurred while retrieving the data

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