Performance: Minimize the number of fields fetched from the database

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

it is written:
In order to minimize the size of data fetched from database and to prevent slow queries and excessive memory usage, the compiler optimizer automatically detects which fields from query result records are used.  

But when debugging this Aggregate in a Preparation   A L L  of the fields are fetched from the database no matter if they are displayed in the table on the web screen of not:

Do I understand this wrong or could you please explain me a better example where this compiler performance feature is used?

Hello Martin,

The optimization is made based on two things:

1. Context of usage. The platform needs to know 100% where the aggregate result is being used. For example, if you add an aggregate in a Server Action, and use an assignment to set the Aggregate List to an Output parameter of the Server Action, the platform will not optimize the aggregate, because is not possible (kind of...) to know where and which attributes of the result will be used. In different places you can use different attributes.

2. Usage is not the same thing as display. So, even if you are not displaying the attributes in the screen, but if later you are using them in a screen action, for example, the platform will have to fetch them. Imagine that you have a button (in each row) in the table records where you are showing only two attributes. When you press the button, you are using the table record as source to an Update action, passing the record. In this case, you are using all the attributes.

So, can you explain how are you "using" the results of this aggregate? This can explain why the aggregate is not being optimized.

Cheers!   

Hi Martin,

An important addition to Eduardo's answer is to mention that this optimization is only applied in Production mode. 

Regards,
Lennart

Hello Lennart.

I confess this is news for me. In Traditional Web, the optimizations used to be made always, including in Dev.
You will find many different posts here with people confused why some data was not available.

But I checked and it seems this way (not sure if this is indeed a feature).

Do you know since when this happens?

Cheers.

Hi Eduardo,

I know what you mean, that sometimes in DEV some data isn't available during debugging. I don't know why this is, because this sometimes also affects data that is being used in the logic.

What I was referring to is the fact that the query is built differently by the system. In DEV all fields are fetch in the SELECT that is built when using an aggregate. In Production the fields that are not being used are fetched as NULL.

For the user entity this means that DEV translates an aggregate to:
SELECT TOP (32) ENUser.[ID] o0, ENUser.[NAME] o1, ENUser.[USERNAME] o2, ENUser.[PASSWORD] o3, ENUser.[EMAIL] o4, ENUser.[MOBILEPHONE] o5, ENUser.[EXTERNAL_ID] o6, ENUser.[CREATION_DATE] o7, ENUser.[LAST_LOGIN] o8, ENUser.[IS_ACTIVE] o9 FROM [OUTSYSTEMS].DBO.[OSSYS_USER_T18] ENUser

If you only use ID, NAME and USERNAME in your logic, in Production this will become:
SELECT TOP (32) ENUser.[ID] o0, ENUser.[NAME] o1, ENUser.[USERNAME] o2, NULL o3, NULL o4, NULL o5, NULL o6, NULL o7, NULL o8, NULL o9  FROM [OUTSYSTEMS].DBO.[OSSYS_USER_T18] ENUser


Hi All,

just to add to the confustion ;-)

I just did a test : personal environment, tradional web, aggregate in preparation, and only show the count on screen.  first tried with user, but in debug, could see all fields.  Then tried with espace-version, because that has a binary, and then indeed, most of attributes are still filled, but the oml attribute is -optimized-.  So maybe the optimization takes into account how big things are, but also maybe the optimization is influenced by the fact that we are debugging ??

Thanks so far.

I used the result in a table as GetContact.List. And of course this is a just a DEV Environment with a small set of data. This is not a real problem, I just try to continue my education especially now in OutSystems performance and I was wandering how this works. Maybe I give it a try in a production Environment.

I am trying to get an answer from the OutSystems team so we can clear this out.


P.S: I may be wrong, but I am pretty sure the optimization was executed independently of the type of the Environment, and I saw it many times in the past. 

Solution

HI, 

I received answer from the OutSystems. 

In platform version 11, the Optimization happens in all environments (Production or Not).
But the aggregates are not optimized when executed inside a Debug session. 

This is different from the behavior observed in older versions of the platform (O9 and O10).

Hope this helps clarify things :) 

Cheers 

Solution

Thx Eduardo for clarification.