Using Aggregate to get only the count

Nelson Freitas

Solved

Question

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

13 Apr 2016

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:

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
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

See solution in context

16 May 2016

1 reply

16 Jun 2020

Show thread

Hide thread

Alvaro Pinilla Pedroche

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:

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
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

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.

16 Jun 2020

André Siébra

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.

13 Apr 2016

Curt Raddatz

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

13 Apr 2016

Nelson Freitas

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

15 Apr 2016

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:

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
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

16 May 2016

1 reply

16 Jun 2020

Show thread

Hide thread

Alvaro Pinilla Pedroche

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:

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
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

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.

16 Jun 2020

Smita Dalai

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

Context menu.png

11 Apr 2018

Smita Dalai

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

12 Apr 2018

1 reply

13 Apr 2018

Show thread

Hide thread

João Heleno

MVP

Smita Dalai wrote:

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,

13 Apr 2018

Abilio Matos

Smita Dalai,

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

Abílio Matos

12 Apr 2018

Justin James

MVP

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

J.Ja

13 Apr 2018

Nelson Freitas

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

16 Jun 2020

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

See the full guidelines