Hi guys,

so, here in my company we are having a small discussion regarding Aggregates and counts.

Imagine that you have an action with an Aggregate and an assign that gets the Aggregate.Count property only. And you don't use any retrieved data.

Will the Aggregate do 2 queries (one to get the data, other to get the count) or will it be clever enough to know that it just needs to query the count ?

I know for sure that back in Simple Queries it would do 2 queries anyway, just don't know if there were any improvments on this for Aggregates.

Do you guys know?

Thanks,

Nelson
Good question. According to the Performance Best Practices document, the platform optimize the number of fields to be retrieved in aggregates (just it did with Simple Queries), but I'm not sure if no field is fetched the platform "clean" this aggregate query execution. Anyway, I agree that the platform should know that it just needs to execute only the count.
Or just use SQL just as you would on any other platform.  This guarantees that only one query is performed.  Something like:

SELECT COUNT(*) FROM {EntityName} WHERE yada yada .....

Hope this helps,
Curt
Hi Curt,

in fact I do prefer Advance Queries, but if the Aggregates do that, we should take advantage of all the optimizations that the platform does.

That's why I'm asking!

Thanks,

Nelson Freitas
Solution
Hi guys,

just to update you all. I've been doing tests on this with our DBA, and here's what I've found:

  1. Using an aggregate to fill a tablerecord, the Platform is optimizing to get only the needed columns. The executed SQL is something like "SELECT col1, col2, null, null, null, col6, from table..." This we all already knew
  2. Using an aggregate and get ONLY the count (Aggregate.List.Count), it will execute 2 queries. The first is "SELECT null, null, null, null, null, null from table..." and the second is "SELECT count(1) from table...". Yet, currently you can use "Group By" in aggregates, so you can you the Count by selecting the unique identifier from the query.

    Then you can get the count with Aggregate.List.current.count

So, if you don't have weird filters that need to be done in Advance Query, you can use this Group By to get the count, otherwise, use advance queries.

Of course, if you just want to know if the query returns data or not, just use the empty property...

Thank you guys for all the help.

Nelson Freitas
Solution

Hi,

I am trying to use the Average () in the context menu, but I am not getting any option for it. Kindly help me with it. I am attaching the screenshot FYI.

Thanks,

Smita

Hi

I am trying to create an average of "rating" which is an existing column. But "average" option is not there. Where can I find that. Kindly help me with this.

I am attaching the screenshot FYI.

Thanks

Smita

Smita Dalai,

This an old topic. Please don't revive them. Create a new one with your issue.


Abílio Matos

You also don't have "Sum". I bet this is NOT Numeric data.

J.Ja

Smita Dalai wrote:

Hi

I am trying to create an average of "rating" which is an existing column. But "average" option is not there. Where can I find that. Kindly help me with this.


Dear Smita,

I've explained you in this topic => https://www.outsystems.com/forums/discussion/35270/not-able-to-find-avg-aggregate-function-in-the-context-menu/ what to do in order to work with averages.

If you still have doubts please use the aforementioned topic to continue this discussion.


Thanks,

JH

Nelson Freitas wrote:

Hi guys,

just to update you all. I've been doing tests on this with our DBA, and here's what I've found:

  1. Using an aggregate to fill a tablerecord, the Platform is optimizing to get only the needed columns. The executed SQL is something like "SELECT col1, col2, null, null, null, col6, from table..." This we all already knew
  2. Using an aggregate and get ONLY the count (Aggregate.List.Count), it will execute 2 queries. The first is "SELECT null, null, null, null, null, null from table..." and the second is "SELECT count(1) from table...". Yet, currently you can use "Group By" in aggregates, so you can you the Count by selecting the unique identifier from the query.

    Then you can get the count with Aggregate.List.current.count

So, if you don't have weird filters that need to be done in Advance Query, you can use this Group By to get the count, otherwise, use advance queries.

Of course, if you just want to know if the query returns data or not, just use the empty property...

Thank you guys for all the help.

Nelson Freitas

Hi Nelson,

Do you know if this feature is currently optimized and the platform only do the count query? or the behavior is the same? the platform do two querys despite the data fetched is not used

Regards.


Hi Alvaro,

Honestly I have no idea, as I prefer to use Advance Queries, which gives me the freedom to optimize them according to the DBA needs. 

The SQL engine will always use the same Query statement because its pure text, as for aggregates, whenever you publish the module, it might change the sql statement to something that the platform "thinks" its better performant than the previous version, screwing around with the advanced indexes created by our DBAs.

But you can test it out pretty quick.

Thank you,

Nelson