7

TL;DR

I wrote a query on Account with a subquery for ChildAccounts
It's returning more than 50,000 rows total (25k parent accounts + 60k child accounts)
I have no idea why I'm not running into the query row governor limit

Background

I'm re-writing some code that is run at the end of each month (via a trigger, invoked using the Salesforce Dataloader/the bulk api v1). The current code occasionally runs into both the 50k query row limit and the 10k dml row limit. In a nutshell

  • I'm trying to retrieve all of the AccountContactRelation (ACR for short) records where
    • Contact.Email is in the set of emails from the current chunk (200 records) of the bulk api batch (1000 records)
  • So I can figure out which ACRs I'm missing and need to insert

In my rewrite, I've reduced the problem to the following

If I can figure out how many child accounts we have for the current bulk batch, I can limit the amount I work on to under 10k rows. That'd solve the dml row issue, and perhaps the query row issue too

My first idea was to use a query like [SELECT Id, (SELECT Id FROM ChildAccounts) FROM Account WHERE Portal_Id__c IN :setOfPortalIds].
I've analyzed my account hierarchy, and it is possible (though unlikely) that I could have more than 50k child accounts for a very unfortunate combination of parent accounts. The number of parent accounts is capped at the size of the bulk batch, but it's the number of child accounts I'm worried about.

After considering an alternative approach I decided to return to my first approach, testing to see if an early break; could prevent some child accounts from counting against the query row limit. Something like

List<Account> children = new List<Account>();
for(Account parent : [SELECT Id, (SELECT Id FROM ChildAccounts) FROM Account WHERE Portal_Id__c IN :setOfPortalIds]) {
    if(children.size() > 9000) { break; }

    // standard nested loop for child records for queryMore() safety
    for(Account child : parent.ChildAccounts) {
        children.add(child);
    }
}

Testing this in the dev console, I was seeing some curious numbers.
The query rows didn't seem to be reflecting the total number of parents + children
Using a simple integer counter to break even earlier (after n parents, using values for n like 3, 50, 250, 500) made the numbers even less clear (jumped from like 72 when n = 3 or 50, to 26k when n = 250)

Being the curious sort, I wondered if I needed the break; at all.

The Question

I landed at the following code

Integer i = 0;
Integer j = 0;
List<Account> children = new List<Account>();
for(Account par : [SELECT Id, (SELECT Id FROM ChildAccounts) FROM Account WHERE RecordType.Name = 'Parent' LIMIT 25000]) {
    for(Account chil : par.ChildAccounts) {
        children.add(chil);
        j++;
    }
            
    i++;
}

system.debug(Limits.getQueryRows());    // prints 25000
system.debug(i);                        // prints 25000
system.debug(j);                        // prints 59975
system.debug(children.size());          // prints 59975

Not only am I exceeding the 50 row limit, I'm exceeding it with the child records alone.
The child records just seem to be completely ignored in the query row limit.

I've tested this in the dev console, as well as through the UI by turning it into a detail page button. No governor limit exception in either case.
Is this just some deep magic related to how SOQL for loops with a nested loop over child records work?

This is happening in a full-copy sandbox.

I haven't found much in the way of documentation for this, and this seems to go against previous knowledge. The following seems to be the closest to my situation:

Can anyone offer an explanation for this?
Is it a bug, or can I actually rely on this behavior?

3
  • I probably wouldn't rely on it, and that kind of skew often comes with a lot of headaches.
    – Adrian Larson
    Commented May 7 at 15:19
  • @AdrianLarson I don't think skew is a particular concern here. Between 2-3 child accounts (median) in that dataset. Less than 100 accounts out of 36k that have more than 20 children, and the max is 192 children. Now if it were something closer to the 80k Assets on a single Account I found some years ago, I'd be concerned. Mostly just thinking about the worst-case here to help minimize errors and processing time in the data load. My current code mostly just doesn't filter queries hard enough (which is what led to my rewrite).
    – Derek F
    Commented May 7 at 16:26
  • Oh I misread and thought you had >50k children for a single parent. Whoops.
    – Adrian Larson
    Commented May 7 at 21:36

1 Answer 1

10

As explained in Apex Governor Limits:

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. The limit for subqueries corresponds to the value that Limits.getLimitAggregateQueries() returns. The row counts from these relationship queries contribute to the row counts of the overall code execution. This limit doesn’t apply to custom metadata types. In a single Apex transaction, custom metadata records can have unlimited SOQL queries. In addition to static SOQL statements, calls to the following methods count against the number of SOQL statements issued in a request. (Emphasis added)

In other words, you can technically query 200,000 total records; 50,000 top level records, and an additional 150,000 child records. You get an additional 300 child relationship queries on top of the 100 top-level queries. For example, SELECT (SELECT Id FROM Contacts), (SELECT Id FROM Cases) FROM Account uses one of the 100 top-level SOQL limits, and 2 child-level queries against the "aggregate" limits.

2
  • I'd always interpreted that as only "we could have up to 300 subqueries", but that we were still limited to 50k rows total (given we have Limits.getAggregateQueries() but not something like Limits.getAggregateRows()).
    – Derek F
    Commented May 7 at 15:44
  • @DerekF All of the limits that apply to top-level queries are multiplied by three. I do find it odd we don't have a Limits.getAggregateQueryRows(), but this is definitely not a bug. It's a behavior as old as Apex itself.
    – sfdcfox
    Commented May 7 at 15:47

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .