5
for (Account tmp : [SELECT Id FROM Account WHERE Name = 'abc']) {

     some operation on fetched record

}

This executes the for loop's code_block once per sObject record and I can make record changes there.

for (Account[] tmp : [SELECT Id FROM Account WHERE Name = 'abc']) {

    for(Account ac: tmp){

       some operation on fetched record

    }

}

This executes the for loop's code_block once per list of 200 sObjects and to make changes in record I need to use second for loop.

Basically, I want to understand specific scenarios where "for (variable : [soql_query])" should be used and scenarios where "for (variable_list : [soql_query])" should be used and what are advantages of one over the other.

  • 1
    can you provide working samples? it may be easier to answer your questions with those – kurunve Apr 17 '18 at 6:48
  • @kurunve I have updated the question – RavisH Apr 17 '18 at 7:27
4

The difference is in CPU time and heap usage. The variable_list version uses more CPU time and heap if you need to process every single record. It has two primary purposes: to allow you to efficiently skip over large chunks of records, such as if you need to get 5 records starting from the 20,000th index (OFFSET is limited to 2,000, so this makes it much more efficient), and it allows you to operate on smaller lists of records efficiently, such as when you need to limit heap use or operate on smaller lists for DML operations, as Tushar answered.

You should almost never use the variable_list method, unless you run in to a specific situation where you only need a specific subset of all the records deep into a query without using a ton of heap usage, or you need to operate on small groups of records at once to limit heap use.

4

There are not much difference between them. It only depends of how you want to use them. When you perform for loop over a list it is a less time consume task because for loop only run less number of times. When you need to process your records in chunk then the second approach is better.

The basic difference between them is

  • The single sObject format executes the for loop's once per sObject record. Consequently, it is easy to understand and use, but is grossly inefficient if you want to use data manipulation language (DML) statements within the for loop body. Each DML statement ends up processing only one sObject at a time.
  • The sObject list format executes the for loop's once per list of 200 sObjects. Consequently, it is a little more difficult to understand and use, but is the optimal choice if you need to use DML statements within the for loop body. Each DML statement can bulk process a list of sObjects at a time.

Here is a code sample to give better understandings.

     // Create a savepoint because the data should not be committed to the database
     Savepoint sp = Database.setSavepoint(); 

     insert new Account[]{new Account(Name = 'yyy'), 
                 new Account(Name = 'yyy'), 
                 new Account(Name = 'yyy')};

     // The single sObject format executes the for loop once per returned record
      Integer i = 0;
    for (Account tmp : [SELECT Id FROM Account WHERE Name = 'yyy']) {
        i++;
    }
    System.assert(i == 3); // Since there were three accounts named 'yyy' in the
                           // database, the loop executed three times

    // The sObject list format executes the for loop once per returned batch
    // of records
    i = 0;
    Integer j;
    for (Account[] tmp : [SELECT Id FROM Account WHERE Name = 'yyy']) {
        j = tmp.size();
        i++;
    }
    System.assert(j == 3); // The list should have contained the three accounts
                           // named 'yyy'
    System.assert(i == 1); // Since a single batch can hold up to 200 records and,
                           // only three records should have been returned, the 
                           // loop should have executed only once

    // Revert the database to the original state
    Database.rollback(sp); 

SOQL For Loops

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.