There is no right way, just best practice.

In this case, the best practice says you should go with 1, due to platform optimizations, and ease to read (maintenance point of view). 

If you need to fetch a huge chunk of data things might be different. And probably the query will also get different, requiring an adv. query.

Now exploring the topic further the SQL statement probably fetches all columns of the table from the DB, even the ones that have been deleted inside SS, meaning worse performance (I don't have the numbers). 

Bottom line, IMO you should go with 1 for regular use, if you need to process a huge amount (millions) of rows go adv. SQL, and some pagination for example.

Hi Sravan,

Yes, you are correct when it comes to result sets they both don't make much difference.  Use the Adv SQL whenever you need to write complex queries or get a limited number of columns where your entity has more columns,When you update records (rather for loop) .. etc

But using aggregate over the Adv SQL, outsysetms checks based on the usage of the result set of your aggregate it will only fetch the columns being used at runtime so that it will be optimized and well performed at run time. 

Hope this helps...!

Thanks 

Ram

Hi,

This is an "old" discussion. :)

Take a look at those links:

https://www.outsystems.com/forums/discussion/51006/how-are-aggregates-and-advanced-sql-queries-handled-internally-in-outsystems-plat/

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

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

Probably this can give the context that you need to understand better the differences.

Hope this can help.

Best regards,

Ricardo Pereira