Hello everyone,
When fetching data for a screen, such as using a table widget, we know that using an aggregate is the best option. Aggregates automatically optimize the queries based on the output requirements, are easier to maintain, and keep our application code isolated from the underlying DBMS.
On the other hand, SQL widgets are better suited for subqueries and bulk update or delete operations.
However, my question is: what is the best practice when working inside a server action and needing to fetch data from 5 to 6 entities using joins? Should we use an aggregate or SQL?
Hi,
1 line answer is -: Go with aggregates.
Reason -: First you need understand how platform server optimizes the aggregates. Suppose there is a table (tabA) which has 10 columns (a,b,c,d,e,f,g,h,i,j). In your case, if you need to use only 3 of them (d,e,f). As a human being how will you write an optimized query ? You will write 'select d,e,f from tabA'. This is what outsystems platform server does automatically and this is the only optimization which you can do when you go with SQL widget.
So instead of tailoring a new thing, it is always good to utilize the readymade thing.
Now if we talk about joins. Same thing applies here as well. If you have good understanding of joins, use aggregates with joins instead of writing a query manually. It will save your time. If there are huge operations which are not doable with aggregates or if there is a use of 'In', 'Exists', 'Any' operators then you can go with SQL widget.
Hi Priya Naveen,
As per my understanding, when fetching data involving 5-6 entities with joins in a server action, Aggregates are typically the best option as they are optimized by OutSystems for performance, provide a visual and maintainable interface, and benefit from platform updates. However, if the query involves complex logic (e.g., nested subqueries, advanced filtering, or unsupported SQL functions), using an SQL widget is a better choice, offering greater flexibility and control while ensuring the query is well-optimized.
Thanks
Hi @Priya Naveen
if the relationships between entities are straightforward and the number of records you're dealing with isn't huge. go with an Aggregate
if you need more control or performance optimization like advanced join conditions, subqueries, or complex filtering or if you need to execute bulk operations like updates or deletes on those entities. then go with SQL
please Refer below Link which gives you more clarity.
https://itnext.io/outsystems-101-aggregates-vs-sql-queries-ec7223f4c496
Regards ,
Rajat
Hi @Priya Naveen ,From my understanding, the best option for me is SQL queries because, in SQL, you can retrieve only the required columns as output from 5 Entities and use subqueries, filters, and more. However, when aggregating across five entities, it will return all columns as output. So, I will go with SQL for this case. Thanks.
Refer the documentation for best practices in SQL queries. Performances Bet Practices