Limit number of elements returned in aggregate

Good afternoon all, in December we went live with our first Outsystems application.   All has been going well but now that we have data piling up we are starting to see some slow queries.  We have one aggregate that joins 5 tables but we really only need 1 or 2 columns from each of the tables.   However the way OS creates the aggregate we end up getting 100 columns back each time the aggregate runs.  

I only really need 10-15 columns.   Is there some aggregate setting I can use to select which columns I actually care about?

Hi Josh,

In OutSystems platform, the compiler optimizer automatically detects the used fields of the aggregate and retrieve only those field values which are used/mapped (with any element) within the screen scope and this is how the Platform optimize the Aggregate element. Similarly in your mentioned case, the used/refereed 10-15 column values is only retrieved and the rest of the columns are just visually displayed in the aggregate view (in the Service Studio).


Check this:

https://success.outsystems.com/Documentation/Best_Practices/Performance/Performance_Best_Practices_-_Queries#Avoid_using_isolated_Aggregates

https://itnext.io/outsystems-101-aggregates-vs-sql-queries-ec7223f4c496


Hope this helps you!


Regards,

Benjith Sam

Thank you, our DBA mentioned that he saw 107 columns but looking closer most of them were showing null so I assume the platform sees all of the columns and then marks the non-used as null.

Hi Josh,

If you use your aggregate in server actions and return the list OutSystems can not optimize your query to the database. This optimization only happens automatically when using the aggregate in screen actions or preparation.

You can optimize this yourself to define a structure that you use as an output parameter to your service action that only contains the attributes you are interested in.

Another alternative is to change the aggregate to an Advanced SQL that just returns the columns you need.

Two more further suggestions:

Regards,

Daniel


Thank you for the tips.   I'm not using the aggregate server side so that's a plus, I did realize yesterday that I hadn't set the max rows on that particular aggregate so that may also have some impact.

I will analyze the columns and see if there are any that we can index.   I'm sure there probably are.

Hello Josh.

I could comment that tables with an average of 20 fields are not great for performance, but I have many examples over 50 to prove that some times it is the only way. And I know your pain.

The Structure/Advanced SQL options are the standard. Think of them as views. From what you said that is not an issue, but keep it for future reference.

Also, your performance is probably affected by the increasing number of rows on those tables. Check the indexes and statistics or it will get worse.

Our DBA just suggested the index below.    I know I can set the indexes at the entity level, but I don't believe we only have the option to select the ON attributes, not INCLUDE.  Is that correct?   If so, we might just have to do this directly in SSMS.


USE [OutSystems_Prod_Platform]

GO

CREATE NONCLUSTERED INDEX [IX_OSUSR_TDK_CASES_OWNEDBY_STATUS_nc_nu_incl]

ON [dbo].[OSUSR_TDK_CASES] ([OWNEDBY],[STATUS])

INCLUDE ([CREATEDDT],[LASTUPDATEDDT],[SUBMITTERID])

GO