What is the difference between soql for loop vs soql list

Question

As per the documentation it mentions ) (soql for loop) retrieves all sObjects using a call to query and queryMore whereas (list for loop) retrieves a number of objects records. It is advisable to use (soql for loop) over (list for loop) to avoid heap size limit error.

Total Heap Size Limit : 6 M.B Synchronous and 12 M.B Asynchronous.

In below case, let say each record is taking 2 K.B so 50,000 will take 50,000*2=100000 K.B (100 M.B approx in conList) which will cause heap size limit error as the allowed limit is 6 M.B for synchronous.

List<Contact> conList=new List<Contact>();
conList=[Select id,phone from contact];

To avoid this we should use "SOQL for loop" as con variable highlighted below will have 1 record at a time i.e 2k.B of data at a time thus preventing heap size limit error.

for (List<Contact> con: [SELECT id, name FROM contact]){
...
}

Question: What does it mean that SOQL for loop" as con variable highlighted below will have 1 record at a time i.e 2k.B of data at a time.

Answer 1

I think you're accidentally conflating the two approaches we can take when it comes to SOQL for loops.

1. Have the loop variable be a single SObject instance (e.g. for(Contact cont :[query here]))
2. Have the loop variable be a List<SObject> (e.g. for(List<Contact> contactGroup :[query here]))

The single object loop variable version is generally what you'll see.

I think the list loop variable approach is mainly a holdover from years ago when we had a "script statement" governor limit instead of the CPU governor limit (but it may still be useful in some situations).

In the first approach, for(Contact cont :[query here])

the loop variable cont only holds a single SObject instance at a time (because that's as much as it can hold). The result of the query itself holds some number of records (up to 2000, I believe), which may also count against your heap space¹. Once you iterate over the first batch of records, an internal call is made to queryMore() to fetch the next set of records to keep feeding your loop.

In the second approach, for(List<Contact> contactGroup :[query here])

This seems to be the approach you're asking about.

The loop variable is a List, which can hold more than one SObject instance. The quote that you've pulled from documentation doesn't apply to this approach. Salesforce will fill contactGroup with 200 records at a time instead of just a single record.

From there, you'd then either use a nested loop to operate on one at a time or pass to another method (which would likely contain a loop to iterate over them one at a time).

In both approaches

Having the query feed the loop directly will help reduce the heap space used because you only have a portion of the records returned by the query "in memory" at any given time.

Once a piece of data is no longer used, the heap space it consumes can be freed (which happens as part of the "garbage collection" that is occasionally run by Salesforce).

Of course, if you have a collection defined outside of your loop, and simply add entire records to it inside your loop, you've negated the heap space advantage that the SOQL for loop would have given you.

// If you do something like this, you negate the advantages of a SOQL for loop
List<Contact> contacts = new List<Contact>();

for(Contact cont :[query here]){
    contacts.add(cont);
}

Also, keep in mind

While Salesforce only counts each record as 2kB towards your org's data storage limit, when you work with that data in Apex it can take up more (or less) heap space than that.

When working in Apex, every bit is counted.

List<Contact> list1 = new List<Contact>();
List<Contact> list2 = new List<Contact>();
Integer count = 2000;
Integer heapBefore1, heapBefore2, heapAfter1, heapAfter2;

heapBefore1 = Limits.getHeapSize();
for(Integer i = 0; i < count; i++){
    list1.add(new Contact(LastName = 'Holmes'));
}
heapAfter1 = Limits.getHeapSize();

heapBefore2 = Limits.getHeapSize();
for(Integer i = 0; i < count; i++){
    list1.add(new Contact(LastName = 'Watson', FirstName = 'John'));
}
heapAfter2 = Limits.getHeapSize();

system.debug('heap usage 1: ' + (heapAfter1 - heapBefore1)); // prints 52020
system.debug('heap usage 2: ' + (heapAfter2 - heapBefore2)); // prints 86016

^{1: I'm not entirely sure how to test this to determine if it's true or not. My first attempt at figuring that out appears to show that this may be true for the first 200 records or so, but then my heap space dropped greatly for the remaining 219 records I had. Perhaps this is Salesforce realizing I'm not doing anything with the data, and optimizing some things out.}

Answer 2

It isn't the variable's content (one SObject) that dictates the amount of heap space but rather by how large a chunk of data that the platform loads in one go.

The for loop approach is, as per the documentation you mention, ensuring that the over-all results are loaded in multiple, smaller chunks but not just one record at a time which would be highly inefficient from the database interaction perspective.

Exactly how large the chunk size is is something that the Salesforce runtime environment decides for itself and is invisible to the developer other than in reduced peak heap space use.

On the other hand, if you directly query and store the resulting list in a list variable, the whole result set is loaded into memory in one go, thus impacting the heap space used at that time.

Thus, if heap is a concern either use SOQL for loops or even a batch (which can handle even larger amounts of data since the whole query process gets split across multiple, separate transactions against the server).

NB: there is no point in first setting a list to a new list value then assigning the list from a query result; the first assignment requires an empty list to be constructed and assigned, while the second causes that first list to be discarded, and to be replaced by a completely new list.