I came accross the following in the Queries best practices documentation (https://success.outsystems.com/documentation/best_practices/performance_and_monitoring/performance_best_practices_queries/)
I understand how limiting the records would improve the screen aggregates importance (since fewer records means less data to send to the client side hence faster), but I assuming that setting the Max property has no affect on an aggregate in a server action which is just used to get data for some other part of the server action but not actually returned by the server action to the client side?
Just asking to make sure I'm understanding the importance of this.
Hi Nicholas,
If you use a Max. Records on an Aggregate, then the SQL query send to the database will have a TOP with that max. number of records. If you use a Max. Records on an SQL, the SQL query send to the database is not affected, so it will retrieve all records that are found by the query, while only returning the first Max. Records to the application. This is of course a bit wasteful, and may or may not incur a performance penalty (depending on the nature of the SQL).
This clarifies nicely, thanks Kilian. I didn't spot the difference originally between the aggregate and SQL max behaviour.
Yeah, it would've been better if this would be consistent, so also add a TOP for SQL, but I can understand that with the complexities of parsing SQL it's something the Platform doesn't do.
the way i understand it, it is applied somewhere between the dabase server and the application server, i think maybe in the application logic right after getting data back from the database. EDIT : what Killian says, not 'somewhere between the database and application', but in the actual query sent, my bad.
So all subsequent code that does stuff with the result of that aggregate, is only doing it to the maxRecords, regardless of whether it is returning data to the client side, or doing forEaches on it, or passing it into other server actions as input : all of that is more performant because it is only dealing with maxRecords records.
You can easily test this, i did a quick test, by having an aggregate in a server action, and only logging the list length, nothing else.
Dorine
"Unlike Aggregates, you need to add a QSL clause to the statement of the SQL element"
The text above that i quoted from the screenshot in your post indicates that the performance on the server side can also improve by setting the MAX records in an aggregate, as it would be similar to limiting an advanced SQL query using "TOP".
Thanks all for the quick responses. I am glad I asked as I now understand much better.
I marked Kilian's as the answer as it made the most impact to my understanding.