4

I have a pretty simple/general question, out of the two approaches below, what is the best method when querying and looping over a collection?

Approach 1:

for(Account a : [SELECT Id, Name FROM Account]){
     //do some stuff
}

Approach 2:

List<Account> acctList = [SELECT Id, Name FROM Account];

for(Account a : acctList){
       //do some stuff
}

I recently ran into a limitation with subqueries using approach 1 that didn't apply with approach 2, I'm wondering if there are any other pros/cons to using one as opposed to the other?

  • I don't think , there is much difference in these two apart from saving a variable in the first approach. You can use the sub queries in the first approach as well . for (account A : [select id ,(select id from contacts) from account]{ }... You can access the related contacts with A.contacts... – amidstCloud Jan 26 '16 at 19:02
  • Yeah, I was doing that but received an "Aggregate Query has too many rows for direct assignment, use FOR loop" when I subqueried using Approach 1. I didn't receive that error when I used Approach 2. (there were more than 500 child records in the subquery for one parent) – sfdcJameson Jan 26 '16 at 19:04
  • are you using an aggregate function in the sub query ? – amidstCloud Jan 26 '16 at 19:07
  • No, just a nested/subquery. – sfdcJameson Jan 26 '16 at 19:07
  • These guys were having the same issue: developer.salesforce.com/forums/?id=906F000000093HqIAI – sfdcJameson Jan 26 '16 at 19:10
4

You get a significant heap benefit by using the former. It induces batching (see SOQL For Loops) so it doesn't have to load the whole list into memory at once. Money quote (emphasis mine):

SOQL for loops differ from standard SOQL statements because of the method they use to retrieve sObjects. While the standard queries discussed in SOQL and SOSL Queries can retrieve either the count of a query or a number of object records, 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.

  • 6
    Also, you can break the loop early without burning through your entire query limit (50,000 rows per transaction). I recently discovered this myself. It's pretty cool. For example: Integer counter = 0; for(Account record:[SELECT Id FROM Account]) { counter++; if(counter==5000) { break; } } – sfdcfox Jan 26 '16 at 19:27
0

I don't think , there is much difference in these two apart from saving a variable in the first approach. You can use the sub queries in the first approach as well . for example

for (account A : [select id ,(select id from contacts) from account]){ 
         system.debug('--' + A.contacts);
}

... You can access the related contacts with A.contacts..

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.