Hi all,
I’m curious how to minimize the number of column to retrieve in an aggregate. Currently, I am using the group by feature in aggregate which then create a structure from the output. Are there other ways to achieve the same output?
What I can think of are:
- get the aggregate
- create the structure (example 5 columns)
- set the value from the aggregate to the structure.
Ok,
for aggregates, the platform takes care of that for you, it will not query or return data that is not used. Doing a group by is absolutely the wrong thing to do here, that's not what group by's are for.
for sql nodes, you need to do that yourself, you'd typically make a structure of only those fields you want, and have that as the output type of your sql.
Dorine
Hi Drexxor ,
To reduce the number of columns ,the best way to implement i.e.advance SQL.You need to write SQL query.
By using SQL,performance is also good.
hello @Drexxor ,
if you want to minimize the number of columns retrieved in an aggregate you can use Advanced SQL. For eg ' SELECT
Column1,
Column2,
Column3,
FROM Table
WHERE <YourConditions> GROUP BY
Column2 '
OR
Open your aggregate, in the "Entities" tab, click on the specific entity and deselect any columns you do not need now ensure only the necessary columns are checked.
hope this will help you
Thanks...
By using advanced sql, OutSystems won’t optimize the sql queries right ?
How do I deselect the column??
Yes, you are correct. When using Advanced SQL in OutSystems, the platform does not automatically optimize the SQL queries.
How do I deselect the column?
you have to Go to the screen or server action where you have defined the aggregate. Double-click the aggregate to open it. In the aggregate editor, you will see a list of entities and attributes that are part of the aggregate. Expand the entity to see all the available columns. By default, OutSystems selects all columns for retrieval. Uncheck the checkboxes next to the columns you do not need. This will ensure that only the selected columns are retrieved from the database.
thanks,
In this case you should use the built-in aggregate editor effectively in the aggregate editor, you will see the list of entities and their attributes on the left and by default, all columns may be selected so you can deselect columns by unchecking the checkboxes next to the column names that you do not need in your aggregate results.
So only use Advanced SQL when you need more complex query capabilities that are not possible with aggregates .
Hi @Drexxor ,
why are you asking this, what is your purpuse ?
If it is performance : the platform already does that for you, it will not return any data that are not used by the screen or block.
Hi Dorine,
I’m trying to understand how to achieve best practices #2 Minimize the number of fields fetched from the database
https://success.outsystems.com/documentation/best_practices/performance_and_monitoring/performance_best_practices_queries/
Important exta dimension to consider : the above is true for aggregates in a screen or block. For aggregates in a server action, I have witnessed that it only returns to the screen or block what is actually used, but I don't know whether in that case only limited or all columns are fetched from the database into the server logic.
EDIT
Based on some tests I just did, i think aggregates inside data actions are optimized, but aggregates inside server actions are not.
hello @Drexxor
Yes, using advanced SQL queries can effectively minimize the number of columns retrieved in OutSystems, providing both flexibility and performance benefits.
Below is a step-by-step guide on how to implement this approach effectively: Step-by-Step Guide to Using Advanced SQL in OutSystems
1. Create an Advanced SQL Query
In your OutSystems module, go to the Logic tab.
Under Server Actions, create a new action.
Drag and drop the SQL widget into the action flow.
2. Write Your SQL Query
In the SQL widget, write your custom SQL query to select only the necessary columns and apply any required filtering or grouping.
Example:
```sql SELECT Column1, Column2, Column3 FROM YourEntity WHERE Condition GROUP BY Column1, Column2, Column3 ```
3. Define Output Structure: Define an output structure that matches the columns selected in your SQL query.
In the SQL widget, click on the “Output” tab.
Define each output parameter to match the selected columns.
4. Map SQL Output to Structure: Ensure the output of the SQL query is mapped correctly to the defined structure.
Sure, I'll make sure to remember the text below:
"This one helps you", thanks