a!queryEntity: An error occurred while retrieving the data

Question

Hi All, 
 I have issue while retrieving data from a view , I have a interface where it has one pie chart and one bar chart and one paging grid where for all these three data is coming from same view but for bar and pie chart data will be fetched again, and data also huge but not more than 5k rows but in view also it's taking 3sec . I am not sure this error is because of huge data or what bcz I could see no error in logs . Could someone help me here please? It's MySQL 
 Thanks, 
 Bhargavi

aloks0189 · Answer

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.

a!queryEntity: An error occurred while retrieving the data

Top Replies