How to fetch more than 1L records using aggreagate
Application Type


We've requirement like in a dashboard we need to fetch all the records from the table using aggreagte and need to show them in the front end.

there's no restriction to use only aggregate even I can use custom SQL but I need a way to do that.

please suggest me what could be the best way to do this.



Perhaps i don't understand your question, because what you ask for is told in the guided paths:

Or do you need something more specific?

Hi Sandeep,

If the challenge is that you don't want to have a slow query when the amount of records are too high, you can use pagination, both with an aggregate or with a SQL widget. 

With a custom SQL, its a bit less straight-forward, as you need to use OFFSET - FETCH, as explained in this thread.

If you really need a query that has to run and go over all the records in a table (i.e. pagination is not useful), then I suggest you create an "intermediate" summary entity. You can periodically calculate the values you want to show on the dashboard from your main entities, and insert the results into that summary entity (with a timer). Then to render the dashboard screen, you query that summary entity.

Agreed. Also, when i read Dashboard i would advise to have more fetch data rather than put all data in one fetch. 

And make your data as lean as possible, for performance reasons.

Hi @Ozan Can Çalı , Correct I don't want to have a slow query and yes the amount of records are too high, I know about OFFSET and FETCH Next rows but that bit complicated here. From your answer I got to know that we can use outsystems default pagination, I've two queries over here, if I use Client Aggregate and pagination in the Screen, do it fetch asynchronously and fills the data to the scrren's table widget or it'll block till the total records gets fetched.

it's okay if I can see 50 recrods per page and loads dynamically while retreiving the data through aggregate.



You can indeed use two different client aggregates if you need two different queries. They will run independently (as long as you don't make them dependent on each other by setting one of them as 'fetch only on demand' and refreshing it in the OnAfterFetch of the other aggregate) and asynchronously.

So, even if they take a long time to fetch, your screen would still render and only the list/table widget would be empty until the data is loaded. To give the "loading effect" to the user during that time, you can make use of GetEmployees.IsDataFetched property and display a loader gif:

However, if your queries consistently take more than a couple of seconds, that usually means that the data needs to be fetched in a more "clever" way (e.g. with better filters in the aggregate or from a smaller summary entity).

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.