3

I get the concept that SOQL has two flavors of for loops and it advises us to use SOQL query in for loop. However, I am not clear on which version we should use when we expect a high number of records from SOQL query. The knowledge available on web leads to some confusion. The two versions of soql for loop can be found in Apex dev guide here and it says that

SOQL for loops retrieve all sObjects, using efficient chunking with calls to the query and queryMore methods of the SOAP API. Developers should always use a SOQL for loop to process query results that return many records, to avoid the limit on heap size.

Then under "SOQL For Loop Formats" heading on the above documentation link it says that "if you intend to use DML inside for loop then use for loop which iterates on a list".

  1. It has also given a code block for both formats (under SOQL For Loop Formats heading on above doc) where it does not process each record with second format. If we want to do so then it will cause inner for loop which is my concern. If we use the second format then won't we end up with for loop inside a for loop because we need to read/process each and every record.
  2. Does the first (and usual format of for loop) retrieves records in batches of 200? I read Why can I loop over the result of a SOQL query as if it is a list of lists? and I am confused if second format is now a hold over, and first format (which is a usual for loop) is good enough to be used in all cases, and it will save you with heap problem.
  3. When the doc says that "SOQL for loops retrieve all sObjects, using efficient chunking" then which SOQL format it is talking about. Which SOQL for loop format uses chunking?
5

If we use the second format then won't we end up with for loop inside a for loop because we need to read/process each and every record.

Yes, but nested for loops aren't inherently evil and don't always need to be avoided at all costs. If you have a list of data that contains another list of data (easiest example is probably the result of a SOQL query that contains a parent-child subquery), the natural way to process those results is with a nested loop.

The metric you should be looking at here is the total number of times you're looping.

If you have something like this

// myListA and myListB defined and populated elsewhere
// Just for safety, don't do this at home (or work)
// The following example is purposefully bad and should not be used
//   in actual code
for(ObjA aRec :myListA){
    for(ObjB bRec :myListB){
        if(aRec.Field__c == bRec.Field__c){
            // do work
        }
    }
}

That would loop a total of A * B times (where A and B are the sizes of their respective lists). Even with modest values of A and B, the number of times we loop is large. This is what we want to avoid.

The "chunked list of lists" approach adds a few extra loop iterations into the mix, but it's pretty tame.

for(List<MyObj> objList :[SELECT Id FROM MyObj]){
    for(MyObj objRec :objList){
        // do work
    }
}

The outer loop runs a single new iteration per 200 records, and then the inner loop iterates over each chunk of 200 records. If this query returns X records, the number of times we loop with that construction is X + X/200. If X is 10,000 that means we loop a total of 10,050 times.

Does the first (and usual format of for loop) retrieves records in batches of 200?

I think that would take someone from Salesforce to confirm or deny. In the end though, the answer to that question doesn't really matter to us developers. We can't tweak that number or do anything directly with the queryLocator.

When the doc says that "SOQL for loops retrieve all sObjects, using efficient chunking" then which SOQL format it is talking about. Which SOQL for loop format uses chunking?

They both use chunking. The difference is that the second approach (chunked list of lists) allows you to see the individual chunks as they're retrieved.

| improve this answer | |
  • 1
    Does the first (and usual format of for loop) retrieves records in batches of 200? Yes, it does. I've proven this. – sfdcfox Apr 26 at 1:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.