What is the benefit of using Aggregate Query in Salesforce

Question

I am wondering if there are any benefits of using Aggregate Query in Salesforce. Lets's say we have 2 code snippets here.

Let's say accIdSet is a collection account Id we are working on.

CODE 1 :

for (AggregateResult agg : [
    SELECT Count(ID) cnt, AccountId
    FROM Contact
    WHERE AccountId IN :accIdSet
    GROUP BY AccountId
]) {
    Account acc = new Account(Id = (String) agg.get('AccountId'));
    acc.Total_Contact__c = (Integer) agg.get('cnt');
    updateAccList.add(acc);
}

CODE 2:

for (Account acc : [
    SELECT Id, (
        SELECT Id
        FROM Contacts
    )
    FROM Account
    WHERE Id IN :accIdSet
]) {
    Account accObj = new Account(Id = acc.Id);
    accObj.Total_Contact__c = acc.Contacts.size();
    updateAccList.add(accObj);
}

Both do the same thing count the number of contacts and update the Total_Contact__c field on the Account record.

I have a basic idea of Aggregate Query and its aggregate functions but is there any benefits of using this.

Answer 1

Anywhere that an array of SObjects is queried - e.g. your Select Id from Contacts - those data rows are included in the response and so have to be transferred and consume a bit of heap.

An aggregate query allows the count to be established inside the database and just that number to be returned. It also signals your intent more clearly which is important to help others understand your code in the future.

So yes sometimes you can use either approach, but aggregate queries are worth learning about so you can apply them when they are by far the best choice e.g. large row counts or other operations such as these are needed:

• AVG
• COUNT
• COUNT_DISTINCT
• MIN
• MAX
• SUM

or more exotic operations such as GROUP BY ROLLUP.

(In the past counting via aggregate queries was still subject to the 50,000 row governor limit but that is no longer the case.)

Using aggregate queries gets the work done close to the data where the work can be optimised. To give an example, we had some complex business logic that involved many rows being queried followed by some fairly complicated Apex code iterating over those rows. Not too surprisingly, this hit CPU governor limits and heap governor limits for the more extreme cases. Refactoring this to do the part of the work that was "per row" into an aggregate query removed those governor limit problems. (OK we were lucky that the logic could be restructured in this way; certainly not always possible.)

Answer 2

Salesforce uses some confusing terminology around this topic. For clarity...

• An aggregate query (as far as governor limits and error messages seem to go) is a parent-child subquery (a.k.a. a "left outer join" in more standard SQL)
• An aggregate function is something like COUNT(), MAX(), AVG(), etc...

Aggregate functions can only be applied on the outermost query, and most (if not all) require you to also use GROUP BY. This causes the query to return a List<AggregateResult> rather than a List<SObject>.

Both parent-child subqueries and aggregate functions have their uses. In the particular example that you've given, the advantage of using COUNT() is that you don't need to worry about the number of records returned. It'll get you the number you're looking for with minimal fuss.

With a parent-child subquery, however, if you have enough child records you won't be able to simply call parent.children.size(). At a certain point, Salesforce will try to call queryMore (internally) and you'll be greeted with an error message telling you to iterate over the child records.

That means that your simple parent.children.size() becomes (pseudocode)

for(parent :[parent query]){
    List<child> childList;

    for(child :parent.children){
        childList.add(child);
    }

    childList.size();
}

So in this case, the aggregate functions end up being less work (SOQL does more of the work for you). Things like doing your own rollups are a good use case for aggregate functions as well. In other cases, the parent-child subquery approach may make the rest of your code simpler.

At the end of the day, it's about doing whatever makes your life easier. If there's a reasonable way to foist some of the work onto SOQL (or another tool/feature) that would normally be done via apex, it's worth looking into.