need help with fetching the data using the entity

Question

Hi team i am unable to fetching the data using the query entity, due to some restrictions i have to use the CDT only so i am using query entity for fetching the data, (I want this data for displaying into the read-only grid) i have 2 tables in DB 1st table having the 42 columns and 26191 rows of data so i am using the below code for fetching the hole data 
 a!queryEntity( entity: cons!1stentity, query: a!query( pagingInfo: a!defaultValue( value: ri!pagingInfo(Type:pagingInfo), default: a!pagingInfo(startIndex: 1, batchSize: 10) ), 
 ), fetchTotalCount: true(), ), 
 but using this code i can able to fetch 21568 rows of data only not able to fetch the whole data, 2nd table having the 8 columns and 389434 rows of data so i am using the below code for fetching the whole data. 
 a!queryEntity( entity: cons!2ndentity, query: a!query( pagingInfo: a!defaultValue( value: ri!pagingInfo, default: a!pagingInfo(startIndex: 1, batchSize: 10) ), 
 ), fetchTotalCount: true(), ), From this table i can able to fetch the 389434 rows of data, i mean i can able to fetch whole data, anyone help me with this how can i achieve to fetch the whole data from 1st table without losing

Dimitris Soulovaris · Answer

Does the first table have duplicate rows?

Mike Schmitt · Answer

The simple answer is: a!queryEntity() has a BUILT-IN SIZE LIMIT (i.e. total amount of data returned, unrelated to the overall number of rows). 
 And also: even if you COULD successfully query 21,568 rows of data successfully into a local variable, if you tried to display that all at once in a read-only grid, the form would either refuse to load or be MISERABLY slow. And if you're planning to page it anyway, then you should be paging it at query time, not after.

Mike Schmitt · Answer

It'll time out if the query is not performant (i.e. slow views, etc). But the Memory Circuit Breaker is separate from this and will prevent a data set over a certain size from being returned.

Mike Schmitt · Answer

ram said: To display the data in Read-only grid also need TotalCount 
 I'm not sure how this is relevant - totalCount is returned even when you take a very small slice of the data in your query, as long as you set the "fetchTotalCount" parameter to TRUE.

Dimitris Soulovaris · Answer

The best way is to use record for sure. 
 But I guess you could do this with queries and a read only grid field with custom pagination. 
 You will need a query to fetch the total count, and with another query and custom pagination buttons, you could load the next/previous batch of data that you want to present in the grid at that time. So you won't query the entire table but only the particular batch of data. 
 I don't recommend this way as it requires a lot of effort to implement it and its tricky as the CDT should be mapped to a table with auto increament ID values enabled so you could count what the startIndex of the pagination will be each time. If the table changes drastically (i.g a view), then its an issue.