Using Aggregate to get only the count

Using Aggregate to get only the count

  
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