Aggregate Vs SQL Query in Server Action

Aggregate Vs SQL Query in Server Action

  

At below link I found section "Avoid using isolated Aggregates". Does this mean that aggregates should be avoided totally in server actions? Or does it only apply when server action output is the aggregate result? What if aggregate output is used within the server action for instance to get a count for further processing, and aggregate result is not the action output?


https://success.outsystems.com/Documentation/Best_Practices/Performance_Best_Practices/Performance_Best_Practices_-_Queries

Hi,

You should avoid using when you output the aggregate result. Because this way outsystems doesn't know which columns will be used and need to get them all from database and all the records. For example if you use an aggregate in a screen preparation outsystems only gets from database the columns that will be needed for that screen and if you have pagination will only get the first records.

Btw remember that when you are using count you are doing a second query to the database. Use length unless your aggregate is used with pagination

Regards,

Marcelo

Hi Marcelo, I totally agree with you on scenario where output of the aggregate is also the output of server action.

Yet not very clear about aggregate result being used within server action, probably the count was not a good example. Let us take the scenario where result of the aggregate is being used within the server action for further processing, but not all the fields from the result are being used. In this scenario, would Outsystems platform still be able to optimize the aggregate based on the fields used?

Solution

Hi Junaid,

Yes. If you only use some of fields and don't output all of them, outsystems will be able to understand which columns needs to bring from the database. For example you can also have a structure with the attributes that you need to output. But in this case all the records are brought from dB. Take care when doing this on entities with thousand of records.

Regards,

Marcelo

Solution

Hello Marcelo


Only to make it clear. The optimization made by the plataform, in what concerne to the columns, only happen is preparation ? or also in other screen actions?


Best Regards


Hi Alberto,

It happens everywhere you use an aggregate and Outsystems can understand that all the attributes aren't used. Let me try explain better this optimizations.

There is 2 optimizations that the platform does on aggregates. The number of attributes and the number of records. 

The optimization of the number of records only happens when the aggregate is linked to a pagination. Outystems brings only the records needed. For example in a pagination of 10 elements for the first page Outsystems brings 10 records, for the second page it brings 20 records and so on.

The optimization of the number of attributes it can happen in any place, not just preparation, where Outsystems can understand that not all of them are needed. For example a server action where you do an aggregate but you only use the id Outsystems will only get this attribute from the DB, but if you do the same aggregate and assign it to a output variable, since you are assigning all the attributes Outsystems will think that all of them are needed and bring all from DB. This will be the same on screen actions Outsystems will only get the attributes that are assign or used on the action or the screen.

Regards,

Marcelo

Thanks Marcelo