How are Aggregates and Advanced SQL queries handled internally in Outsystems Platform

Hi All,

How are aggregates and Advanced SQL queries internally handled by the Outsystems platform server? While publishing an Outsystems module with both aggregate and an advanced SQL query both having the same functionality, How is it internally transformed and implemented in the backend SQL server? Whether both Aggregates and Advanced SQL queries are transformed into SQL stored procedures in backend SQL server?

Thanks,

Karthik 

Hello Karthik. 

No. OutSystems does not generate stored procedures. 

When you publish your module, both aggregates and SQL tool becomes code that is executed in runtime, sending SQL statements to the SQL server. 

Even if they have both the same functionality, as the platform can perform optimizations on aggregates that it cannot in SQL tool, the resulting SQL query sent to the database in runtime can be different. 

Is this what you wanted to know? 

Cheers. 

Eduardo Jauch wrote:

Hello Karthik. 

No. OutSystems does not generate stored procedures. 

When you publish your module, both aggregates and SQL tool becomes code that is executed in runtime, sending SQL statements to the SQL server. 

Even if they have both the same functionality, as the platform can perform optimizations on aggregates that it cannot in SQL tool, the resulting SQL query sent to the database in runtime can be different. 

Is this what you wanted to know? 

Cheers. 

Thanks Eduardo


Solution

Hi Karthik,

Aggregates are a simpler way to query the database while Advanced Queries are more flexible. Regardless of the method you choose, SQL will be generated (you can consult the exact query by checking the Executed SQL tab of both elements) and sent to the database.

There's a good primer on the differences between both elements here, written by Justin James. But if you're interested in the internals, and what exactly gets executed in the database, I'd surmise that the end result is not very different - barring Aggregate optimisations done by the platform.

Solution

Afonso Carvalho wrote:

Hi Karthik,

Aggregates are a simpler way to query the database while Advanced Queries are more flexible. Regardless of the method you choose, SQL will be generated (you can consult the exact query by checking the Executed SQL tab of both elements) and sent to the database.

There's a good primer on the differences between both elements here, written by Justin James. But if you're interested in the internals, and what exactly gets executed in the database, I'd surmise that the end result is not very different - barring Aggregate optimisations done by the platform.

This is a more relevant and satisfying answer. 

Thanks, Afonso 

Glad I could be of help!