Aggregate or SQL Widget
Application Type
Traditional Web

what is more preferable aggregate or SQL widget ?

mvp_badge
MVP
Solution

hi Ajit Kurane,

I’m pretty sure there ar plenty of posts explaining why, as well as best practices entries in the documentation… but I’ll give you the basic points here:

If you can do it with an Aggregate, most likely it’s better to use Aggregates, they are automatically optimized based on what you are using from their output, they are easier to maintain and they keep your application’s code isolated from whatever DBMS you are using.

There are however some situations where you cannot use Aggregates: if you need to use SQL’s IN or EXISTS keywords; if you need to use sub-queries of any kind; if you need access to specific features of your DBMS provided by their specific SQL dialect. In this case you should use the SQL tool, but be aware that you will have to optimize the SQL statement by hand, and you will have more effort maintaining it as you evolve your data model.

There’s also at least one scenario where you can use both an Aggregate or SQL tool, but the best practice is to use the SQL tool: when you are doing bulk write operations on the database (INSERT, UPDATE and DELETE), as you will be able to execute them in a single SQL statement (and one request to the DB) as opposed to using an Aggregate and multiple Entity Action operations (multiple requests to the DB).

Hope this helps!

mvp_badge
MVP

Hi Ajit,

If you can complete the task with an aggregate, that is always preferable over an SQL widget, it requires less knowledge and is less error prune. Furthermore, with an aggregate OutSystems is able to optimize the generated code for performance.

If you would have googled this question, many forum discussions as well as articles on this topic will be shown.

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

https://www.outsystems.com/forums/discussion/42037/aggregate-vs-sql-query-in-server-action/

Regards,

Daniel

mvp_badge
MVP
Solution

hi Ajit Kurane,

I’m pretty sure there ar plenty of posts explaining why, as well as best practices entries in the documentation… but I’ll give you the basic points here:

If you can do it with an Aggregate, most likely it’s better to use Aggregates, they are automatically optimized based on what you are using from their output, they are easier to maintain and they keep your application’s code isolated from whatever DBMS you are using.

There are however some situations where you cannot use Aggregates: if you need to use SQL’s IN or EXISTS keywords; if you need to use sub-queries of any kind; if you need access to specific features of your DBMS provided by their specific SQL dialect. In this case you should use the SQL tool, but be aware that you will have to optimize the SQL statement by hand, and you will have more effort maintaining it as you evolve your data model.

There’s also at least one scenario where you can use both an Aggregate or SQL tool, but the best practice is to use the SQL tool: when you are doing bulk write operations on the database (INSERT, UPDATE and DELETE), as you will be able to execute them in a single SQL statement (and one request to the DB) as opposed to using an Aggregate and multiple Entity Action operations (multiple requests to the DB).

Hope this helps!

Hi @Ajit Kurane ,

Aggregates are Optimized, So unless & until we go for bulk operations we might consider Aggregates.

you can refer below documentation 

https://jmjames.medium.com/aggregates-or-advanced-sql-when-to-use-which-one-8f052c0de324



Thanks,

Sandeep.


Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.