14

I just found a construct similar to the following in a colleague's code, which I didn't expect to even compile:

for (List<Lead> leads : [ 
    SELECT Id FROM Lead LIMIT 1000
])
{
    for (Lead lead : leads) {
        // do some stuff
    }
}

You can paste the code above into an Execute Anonymous window and it will execute without error.

What's going on here? As far as I know, [SELECT Id FROM Lead LIMIT 1000] should return a List<Lead>, but above we're looping over it as if it returned a List<List<Lead>>.

The only documentation I can find about the [SELECT ...] syntax being able to return anything other than a list of sObjects is at https://developer.salesforce.com/trailhead/apex_database/apex_database_soql, where it's noted that including LIMIT 1 in the query will cause it to return a single sObject instead of a List:

Account oneAccountOnly = [SELECT Name,Phone FROM Account LIMIT 1];

but that doesn't explain how my colleague's code can work.

Is the fact that this construct works a bug, or intentional behaviour? If it's intentional, is it documented anywhere, what's it for, and how does Apex decide how to split the results of the SOQL query into the multiple sub-Lists that my colleague is looping over?

16

This is a documented behavior; the syntax is mentioned briefly in the docs on For Loops and more detail on how it behaves is given in the docs on SOQL For Loops. It looks weird, but it works.

When the platform detects a SOQL for loop like this, the underlying platform uses a feature called a QueryLocator, which uses the underlying cursor feature in the Oracle database.

In some regards it is a hold over. At one point in time there was a maximum List size in Apex governor limit. The coding construct helped developers work around it.

Later the List size governor limit was removed, but this feature remains.

Initially upon removal of the list size governor limit, many developers concluded that this feature was unnecessary. However I know of developers who did performance profiling and found that in larger queries the QueryLocator behavior had performance benefits over just iterating over all of the records.

It's also useful for avoiding hitting the heap size governor limit, and for that reason Salesforce recommend using this syntax for queries that return many results:

SOQL for loops differ from standard SOQL statements because of the method they use to retrieve sObjects. ... 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.

When iterating, the outer for loop uses a maximum batch size of 200.

  • 3
    While this answer is mostly very useful, I'm pretty skeptical about this claim: "several enterprising developers did some performance profiling and found that in larger queries the QueryLocator behavior had significant performance benefits over just iterating over all of the records". Do you have a source for this, or rational explanation of why it should be so? In general, I distrust unsubstantiated claims of magical performance increases unless I can either understand why the difference would exist or have evidence of it in front of me. – Mark Amery Oct 19 '15 at 12:14
  • Fair enough. I don't have original source material as all were people in the field doing their own assessment and not publishing to blogs or publicly. However these tests were also 3+ years ago, meaning 8-9 releases ago. With changes in the platform it is probably due a retest anyway. Also, query and transaction profiling tools didn't exist then, so arguably the test should be more conclusive (either at proving or disproving). I don't have the time to do so in the next few weeks. But if someone wants to take up the challenge, I encourage it. – pchittum Oct 19 '15 at 14:07
  • Given that...I will reword. Probably better to be less hyperbolic. ;-) – pchittum Oct 19 '15 at 14:10
  • 3
    Tested. The QueryLocator method tended to be faster, but was also sometimes slower, so that suggests system load plays more of a role than either form. The biggest observed difference was about 10% either way, which can easily be attributed to system load and cache. However, the memory bonus is definitely there. With the QL method, you have a smaller chance of hitting heap limits. – sfdcfox Oct 20 '15 at 19:42
1

Please see the SOQL For Loops article. The construct with the list variable executes in a - potentially - few batches. I would say that it's some kind of exception to Apex grammar, which might seem a bit inconsistent, but is very convenient syntactic sugar.

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.