Hi,
We have 1 million records in database table. We have used aggregate to fetch data from table with page size of 10 records. Aggregates has single table as data source, no joins.
It's taking lot of time to load data of last pages of Table on screen.
How to improve load time of last pages of table?
Best Regards,
Nitin
Hi @Nitin Chavan ,
I'd like to start by saying :
What exactly is your use case for looking at the last page of millions of records? What would your end user hope to find there specifically ? Why not a page right in the middle ? The answer to the question 'what is the user looking for there' can help you improve the experience by giving them the appropriate sorting and filtering.
Making them go to the last page is rarely the answer. For comparison, when you are looking for something on Google, you never feel the need to go to the last page (which isn't even there in the pagination on Google).
But on to your question of slow performance :
As far as I can tell, when executing an aggregate, Outsystems is performing the paging in the front end server after having retrieved all data from the database up to the page they are interested in.
So I think they do a TOP in SQL terms, instead of a OFFSET, and then skip all but the last page of records. This means that only 10 records are brought back to your device/browser, but millions of records ar being brought back from the database server to the front end server. That's just my guess, I have never seen any documentation on this.
One option, if you still want them to be able to see the last page quickly (but really, why ?) is to replace your aggregate by an sql, and using OFFSET FETCH NEXT in there.
I made a quick oml to compare both, if I put 6.000.000 rows in a very simple table, the difference for my quick test is
with aggregate
with sql :
attached oml for you to try if you like.
This is not a real test, the table design is too simple for that, and no filters etc. Just for comparing what happens for the last page. Also, I'm not sure the sql being slower for the low pages is significant either, not enough of a difference and again, things like filter are much more important.
Dorine
Hi @Dorine Boudry ,
Thank you for detail explanation :)
In our upcoming project, we will have database table with 50+ columns and millions of records. I have tried creating same scenario in POC (Reactive app) with table having 50+ columns and 1 million records. Then I have used aggregate to fetch records and display it using Table widget with page size 10. In this POC I observed this behaviour.
May be in this case records fetched from database are as below-
Page 1 - Top 10 records
Page 2 - Top 20 records
AND
Last page - All the records
As suggested by @Alim Manurung I have implemented the logic using advance SQL with OFFSET and FETCH. It's loading the first page and last page in same time approx. 5 ~ seconds. Advance query is a good option with few cons like - maintainability issue, query become complex with joins, filters, sorting and pagination code.
Application requirements are not gathered yet so we are not clear about specific use case to visit the last page. Yes filtering and sorting needs to be implemented. We are trying to test query performance with millions of records. We are looking for best options.
yes exactly,
when using aggregates, if you go to last page, all records are sent from database to front end server (i think).
aggregates are, if at all possible, preferable over sql for better maintainability and easier on beginners, their performance at retrieving last page of 1.000.000 records should not carry too much weight in your decision of which to use.
Hi @Nitin Chavan, to improve the performance you can change the fetching aggregate to SQL Offset.
SELECT *
FROM {TableName}
ORDER BY {TableName}.[Id] DESC
OFFSET @StartIndex ROWS FETCH NEXT @MaxRecords ROWS ONLY
Don't forget to refresh the fetch data action in OnPaginationNavigate. This way can help to optimize the performance issue. Thanks
Hi, in addition to what Alim suggested, it seems the first column has the date formatted, are you doing that for each line?
Doing the format for each line will consume more time compared to bringing the date with the correct format from the server call.
Also, 1M records it's a huge bag, are you obliging the user to select any filter before searching? It can be an idea to reduce the effort of the search.
Regards,
PZ
@Paulo Zacarias ,
that formatting won't be it, this probably happens as an expression on his front end, so only for 10 rows.