Aggregate Optimization

  
I was testing the aggregate optimization, like it was made in the Simple Query, and I noticed that this optimization was not being made.
Do you know if there is a problem with this optimization?

Example:

- Aggregate in a Screen preparation

- List using the aggregate

- There is no screen actions on this screen (no TableRecord action usage).

The query made to the Database has all table attributes and not the one's used in the screen, like it was made with the simple query in version 8. It was supposed that all attributes that are not being used, returned null.

Thanks in advance.


Best Regards,
Miguel Sousa
Afaik, 

The optimizations are only effective in production-mode?



Hi Miguel,

The test functionality of the aggregate does not optimize in any way (and I don't recall the Simple Query test functionality doing that either). But rest assured, when your program runs, the platform will perform the necessary optimizations.
Hi,

I'm not talking about the aggregate test but the request that is made to the database in a runtime application.

In Platform 7, if you use a simple query with one or more tables just to fill some table record and the screen actions use only the elements present in the TR, the platform send a request to the database like this one:

SELECT NULL o1, ENALERTA."LABEL" o2, ENALERTA."DESCRIPTION" o3, NULL o4
FROM "HUBADMIN"."OSUSR_X6P_ALERTA" ENALERTA WHERE ROWNUM<=51

The attributes that you don't need on that screen are null (if you use a refresh query on the screen action, it has the same effect).
For some reason, today the aggregate optimization is being made without any change on the screen. Maybe it was a publication problem and the platform wans't able to check if the aggregate was being used by another element.

Thanks anyway.
Best regards
Miguel Sousa wrote:
Do you know if there is a problem with this optimization?

The query made to the Database has all table attributes and not the one's used in the screen, like it was made with the simple query in version 8. It was supposed that all attributes that are not being used, returned null. 
Hi Miguel,

No, there's no problem that I'm aware. Optimizations run both in Development and Production mode, but on the development environment you will see all the attributes filled in in the aggregate. If you want to be sure that optimizations are being executed you should look directly into the database and validate the query that is effectively executed in runtime using a profiler.

Cheers
I think this is a relevant question, so just for future reference let me complement André's reply with an example.

OutSystems Platform optimizes Aggregates for you automatically, based on the attributes you use from an Aggregate. But to ensure you can easily develop your apps without thinking about what will be optimized or not:
  • Aggregates are not optimized during development time. They show you everything you could access;
  • Aggregates are not optimized during a debug session. This allows you to inspect all values, making the debug session more straightforward.
At runtime, the SQL generated is optimized to:
  • Only fetch the columns you really use;
  • Only fetch the number of rows that you'll display.
Here's an example. I've got an application that has an Emplyee entity with:
  • Name
  • LastName
  • EmployeeSince


The Table Records on the employee list screen only displays the employee Name attribute. And its 'Line Count' property is set to 50 (the default).

The Aggregate I've used on the Preparation looks like this


Here's the SQL
SELECT TOP (32) ENEmployee.[ID] o1, ENEmployee.[NAME] o2,
    ENEmployee.[LASTNAME] o3, ENEmployee.[EMPLOYEESINCE] o4
FROM [OUTSYSTEMS].DBO.[OSUSR_O66_EMPLOYEE] ENEmployee
Notice the Aggregate displays the LastName and Employee since columns, and the generate SQL (you can see it on the Aggregate properties), is also fetching all columns.
This is just so that you can develop without thinking about what is optimized or not. This is not the SQL that will be generated at runtime.

And here's a debug session, here I'm inspecting the Aggregate.


Notice that during a debug session, the LastName and EmployeeSince attribute are also fetched.

But this is what will happen at runtime. Having your environment in 'Development' or 'Production Mode' doesn't really matter.


The runtime SQL:
SELECT TOP (51) NULL o1, ENEmployee.[NAME] o2, NULL o3, NULL o4
FROM [OUTSYSTEMS].DBO.[OSUSR_O66_EMPLOYEE] ENEmployee
ORDER BY ENEmployee.[NAME]

Notice how:
  • We are only fetching the employee Name column. All other columns are not selected
  • We are only fetching 51 rows. This is because our Table Records will only display 50 records, and OutSystems Platform will always fetch one more record for pagination purposes.

Hope you now understand the magic behind OutSystems Platform.