3

What is the difference between the below two code snippets

1)

for (Account a : [Select Id from Account]){
}

2)

listofAcc = [Select Id from Account];
for (Account a : listofacc){
}

As per the best practice, it says use the first one. Since it does the query dynamically.

But what the exact difference as the total SOQL queries will be 50K anyhow.

Also, if I have multiple for loops like

for ( ) {
for ( ) {
}
}

which one to use and why?

Any ideas?

3 Answers 3

4

The SOQL For Loops documentation is clear in its recommendation of option 1:

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

To provide an example, say each SObject you query occupies on average 2k bytes. Then using option 2 in the question means at most 6M/2k = 3,000 records will cause the total heap size governor limit to be hit. Using option 1 in the question where blocks of 200 records are returned, that governor limit will not be hit with the platform taking care of the chunking for you.

1

Second approach is better than the first one.

Reason:

  • Salesforce doesn't require to query, iterate and then return instance to traverse for loop. So approach 1 requires more processing than approach 2.

  • You always have scope to reuse queried list.

  • Independent queries run faster than using in for loops

In terms of performance, traversing through a queried list is faster than traversing through non-existing list.

Take the following example with load of data in Account object:-

System.debug('1. '+DateTime.now().getTime());
for(Account acc : [SELECT id, Name FROM Account])
{

}

System.debug('2. '+DateTime.now().getTime());
List<Account> acc = [SELECT id, Name FROM Account];
for(Account ac : acc)
{

}
System.debug('3. '+DateTime.now().getTime());
17:55:37:046 USER_DEBUG [1]|DEBUG|1. 1435580737654 
17:55:37:089 USER_DEBUG [7]|DEBUG|2. 1435580737697 
17:55:37:104 USER_DEBUG [13]|DEBUG|3. 1435580737712

Nested loop is not a good idea in Salesforce. Do every effort to avoid this situation.

4
  • When you reverse the order of option 1 and option 2 in your test, do you still get option 2 as faster?
    – Keith C
    Commented Jun 29, 2015 at 15:06
  • Yes, it doesn't matter whatever the order. Timestamp Event Details 21:47:56:069 USER_DEBUG [3]lDEBuG11.1435594676091 21:47:56:085 USER_DEBUG [9]IDEBUGI2.1435594676106 21:47:56:123 USER_DEBUG l14]lOEBuG13.1435594676144 @KiethC
    – Ashwani
    Commented Jun 29, 2015 at 16:22
  • Yep I get the same result you do. I guess there is a cost to the query/queryMore pattern. But I'll still stick to it.
    – Keith C
    Commented Jun 29, 2015 at 16:41
  • Theoretically your explaination is correct but practically we get different result. qyeryMore requires 200+ results. I tested it on 100 records. @KeithC
    – Ashwani
    Commented Jun 29, 2015 at 16:45
0

First one is better because you don't need to create a different list object for storing Account Records, and you can fetch maximum 50k records in a single execution and never put SOQL query in loop because it exceed limit when you import thousand of records using data loader or import wizard.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .