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 whereContact.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:
- queryMore() rows don't count until access (2022) https://salesforce.stackexchange.com/a/388795/24889
- break can reduce the number of rows counted towards the limit (2016) https://salesforce.stackexchange.com/a/129712/24889
- my assertion that child rows do count (2021) https://salesforce.stackexchange.com/a/348870/24889
- this one doesn't try to access the child data (2022) Subqueries in soql for loop vs standard soql queries
Can anyone offer an explanation for this?
Is it a bug, or can I actually rely on this behavior?
Best Answer
As explained in Apex Governor Limits:
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.