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".
- 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.
- 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.
- 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?