a!queryEntity: An error occurred while retrieving the data

Question

Hi All, 
 
 I am trying to retrieve data from DB by using query entity. I am able to fetch 10,000 records beyond that I am getting "a!queryEntity: An error occurred while retrieving the data". 
 
 I have more than 18000 records in my table which I need to display in a paging grid with filter options. I have check the performance of my view at DB end. It is not even taking 1 second to execute that view not sure why with query entity is not able to fetch all records. When in my paging info I am giving batchsize more that 10000 or -1, it gives me the error below this, it is working fine. Not sure if query Entity has limitation in fetching number of rows. 
 
 Please share, if I can use some other way to fetch all the records from DB and use in my interface. 
 
 Thanks in advance !! 
 
 Regards 
 Anusha

Siva · Accepted Answer

Hi Anusha, Try below code and replace the query entity with your query entity and pass the paging info with batch size as your requirement(It will fetch the batch Size records only each time and it will be speed and won't give you error.) load ( 
 /* Set the default paging and sorting config */ 
 local!gridSelection : a!gridSelection ( 
 selected: {}, 
 pagingInfo: a!pagingInfo ( 
 startIndex: 1 , 
 batchSize: 50 , 
 sort: a!sortInfo ( 
 field: "lastName" , 
 ascending: true 
 ) 
 ) 
 ), 
 with ( 
 local!datasubset : a!queryEntity ( 
 entity: cons!EMPLOYEE_ENTITY , 
 query: a!query ( 
 selection: a!querySelection ( 
 columns: { 
 a!queryColumn ( 
 field: "firstName" 
 ), 
 a!queryColumn ( 
 field: "lastName" 
 ), 
 a!queryColumn ( 
 field: "title" 
 ) 
 } 
 ), 
 pagingInfo: local!gridSelection.pagingInfo 
 ) 
 ), 
 a!sectionLayout ( 
 contents: { 
 a!gridField ( 
 label: "SAIL Example: Employee Grid Selection" , 
 totalCount: local!datasubset.totalCount , 
 columns: { 
 a!gridTextColumn ( 
 label: "First" , 
 field: "firstName" , 
 data: index ( 
 local!datasubset.data , 
 "firstName" , 
 {} 
 ) 
 ), 
 a!gridTextColumn ( 
 label: "Last" , 
 field: "lastName" , 
 data: index ( 
 local!datasubset.data , 
 "lastName" , 
 {} 
 ) 
 ), 
 a!gridTextColumn ( 
 label: "Title" , 
 field: "title" , 
 data: index ( 
 local!datasubset.data , 
 "title" , 
 {} 
 ) 
 ) 
 }, 
 identifiers: local!datasubset.identifiers , 
 value: local!gridSelection , 
 saveInto: { 
 local!gridSelection 
 }, 
 selection: true , 
 shadeAlternateRows: false , 
 selectionStyle: "ROW_HIGHLIGHT" 
 ), 
 a!textField ( 
 label: "Selected Employee IDs" , 
 readOnly: true , 
 value: if ( 
 length ( 
 local!gridSelection.selected 
 ) = 0 , 
 "No employees selected" , 
 joinarray ( 
 local!gridSelection.selected , 
 ", " 
 ) 
 ) 
 ) 
 } 
 ) 
 ) 
 ) Thanks Siva Chimata

chandu · Answer

Hi Anushaj, 
 
Did you get a chance to look in to logs to see what is the exact error message that is written in log. 
The above error message is a generic one any how in your case you are querying more than 10,000 rows so these are the two possible cases. 
1. Property where the default time out configured for query in custom.properties file 
conf.data.query.timeout=10 
2.property where the default memory size to hold the data in the application server configured in custom.properties file. 
conf.data.query.memory.limit=1048576 i.e. 1 MB 
 
Though you have 18,000 records in your DB, pulling 10,000 records at a time and displaying in grid will not be a good idea, 
you can pull the less amount of records in batches example 50 or 100 and when user navigates through pagination then try to pull the next set of records. in this way the we can take care of performance.

Dastagiri Dudekula · Answer

First, consider whether the entire set of data that is being returned is actually needed. If not, use the query expression functions to select which fields to return. 
Second, use the query rules paging parameter to return less data (or return data in batches) and therefore avoid the limit. 
Third, If these solutions still do not meet the requirements of the use case, consider the number of concurrent query rules that are expected in the system and the amount of free memory available on the application server. 
Fourth, If above things does not works, then change the maximum amount of time in seconds that a query waits for a response from the database before timing out and change the amount of memory in bytes that will be consumed in the application server for a single query before the query is halted