0

I am trying to loop over child records via accessing child records through relationship(customer_MDM_Customer_Address__r) and not doing any query inside for loop but still getting too many SOQL queries: 50001 error. Loop iteration is randomly increasing the limits.getQueryRows(). Does looping over relationship object count towards Soql limits?

enter image description here

enter image description here

1
  • 1
    Please provide code examples as text, not as image. It's easier to read and reproduce (by copy-paste); in addition to that, search engines can index it. Oct 28, 2022 at 7:00

3 Answers 3

2

In Apex, when a child relationship exceeds a certain threshold (minimum 200, maximum 2,000, depending on number/types of fields queried), the list changes from an ordinary list into a QueryLocator. If you try to access this kind of list directly, you'll get an exception:

FATAL_ERROR|System.QueryException: Aggregate query has too many rows for direct assignment, use FOR loop

In this mode, the rows in the QueryLocator are not counted against the limits until you attempt to read those records.

QueryLocator locator = Database.getQueryLocator([SELECT Id FROM Account]);
System.debug(Limits.getQueryRows()); // Outputs 0

As I demonstrate in this answer, when you start to iterate over these lists, you're "charged" against the SOQL row limit based on how many records are retrieved (which are in batches of 200). Therefore, it's entirely possible to end up with this exception in the middle of a for loop on the child records.

You'll have to either process or filter for fewer records, watch your limit inside the for loops and abort/throw exception, etc. Or you may need to change this code to asynchronous code, such as a batchable class.

-1

Dhruv - It wouldn't be accessing the child records that is creating this exception, but querying them in the first place. In order to loop a related list via the '__r' API name like you are doing, you have to include that '__r' name in your initial query. Something like this, right?
[SELECT Id, Name, (SELECT Id, Name From MDM_Customer_Addresses__r) FROM MDM_Customer__c];

In your entire transaction, adding together all of your queries, you are being returned over 50,000 records. This number includes the returned child records. Accessing the records won't change that number. (Somebody please correct me if I'm wrong!)

One thing I'm seeing in your screenshot is that the USER_DEBUG statments in your lower log window don't line up to the System.debug statements in your code. Those debug messages are getting generated somewhere else in your code. Perhaps that is causing your confusion.

1
  • Accessing the child records, when certain conditions are met, can increase the SOQL count and even throw an exception in a for loop.
    – sfdcfox
    Oct 29, 2022 at 18:22
-1

From the Apex Limits documentation

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

So, yes, the child rows count against the 50,000 limit

1
  • 1
    Sometimes. It's more complicated than that.
    – sfdcfox
    Oct 29, 2022 at 18:09

This site is temporarily in read-only mode and not accepting new answers.

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