How many SOQL queries are spent for subqueries?

Apex Governor Limits docs state that:

In a SOQL query with parent-child relationship subqueries, each parent-child relationship counts as an extra query.

However, when a query with a single subquery is run, for example:

List<Parent_Object__c> parents = [Select Id, (Select Id from Child_Objects__r) from Parent_Object__c];

the debug log shows that just 1 SOQL query was spent. That makes sense since subqueries are (left) joins.

It’s the actual count, not just the count display because I can run 51 queries and limits won’t be hit (if two queries would be counted and just one displayed, this would be 51 * 2 = 102 > 100 SOQLs).

So what’s correct for total of N parents?

  • 1 extra query for each parent record (1 + N SOQLs in total)
  • 1 extra query for entire relationship (1 + 1 SOQLs in total)
  • no extra queries (1 SOQL in total, as indicated by debug log)

Answer

Let’s take the following SOQL:

List<Account> dd = [
    SELECT 
        id, 
        (SELECT Name FROM Contacts),  // <- first subquery
        (SELECT AccountID FROM Cases) // <- second subquery
    FROM 
        Account
    WHERE 
        id 
    IN (SELECT AccountID FROM Case)   // <- not counted
];

In the above query there are total 3 subqueries + 1 parent query. So, there are 4 queries. But, Salesforce doesn’t count subqueries against governor limit of 100 SOQLs. It counts only root query which means only 1 SOQL query would be consumed.

Subqueries are counted separately as AggregateQueries. You can check those with Limits.getAggregateQueries() and Limits.getLimitAggregateQueries(). You cannot have more than 300 aggregations in a single transaction. This is stated just below the sentence you quoted:

In a SOQL query with parent-child relationship subqueries, each parent-child relationship counts as an extra query. These types of queries have a limit of three times the number for top-level queries

So your count would be:

N = Number of SOQL (root query)
A = Number of Aggregation

Safe Query Limit = N <= 100 && A <= 300

Attribution
Source : Link , Question Author : ipavlic , Answer Author : ipavlic

Leave a Comment