Efficient SOQL For Loop

There are several governor limits to be considered when you are developing on Force.com platform. Among these, hitting the maximum number of queries allowed is the most common governor exception. Run time exception will be thrown as  System.Exception: Too many SOQL queries: 101.

This mainly occurs when you have put SOQL queries inside a loop as below.

for(Integer i=0; i<200; i++){
    Account act = [SELECT Id, Name FROM Account WHERE some_condition];
}

To avoid this you should somehow build the logic to meet the business matter and keep the query outside the loop.

Now the interesting part

Salesforce document says, "Developers should always use a SOQL for loop to process query results that return many records, to avoid the limit on heap size". What does this mean?

When you execute a standard SOQL query, it retrieve all the records while a for loop query does the same in chunks with SOAP API queryMore calls.


In addition to this, there are two formats of SOQL for loop in Force.com

• Single sObject format where the for loop’s code block get executed once per sObject record(mostly used way).
• sObject list format where the for loop’s code block get executed once per list of 200 sObjects

In this article what I need to highlight is the second format since it’s rarely seen in Force.com development. Have a look at the below snippet to get more clear idea

// keeping a savepoint so that transaction can be rolledback
Savepoint sp = Database.setSavepoint(); 

insert new Account[]{new Account(Name = 'AAA'), 
                     new Account(Name = 'AAA'), 
                     new Account(Name = 'AAA')};  //insert some data that can be identified

Integer i = 0;
Integer j;
for (Account[] tmp : [SELECT Id FROM Account WHERE Name = 'AAA']) {
    j = tmp.size();
    i++;
}
System.assert(j == 3); // The list should have contained the three accounts
                       // named 'AAA'
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); 

Notes

You are safe to perform DMLs inside list format for loop queries than in a normal for loop
 query since the records are processed in chunks in list format (anyway this is not
 an encourage to perform DML inside a loop). 
Since the queries having aggregate functions doesn't support queryMore function,
 you might get a runtime exception if you have such a query with more records in SOQL for loops. 
When using the keyword ‘continue’ in list format for loops, it’ll skip to the next list
 of sObjects.