SOQL List vs SOQL For Loop

In this blog post we are going to learn about SOQL List vs SOQL For Loop.

We will try to understand the syntax of SOQL List vs SOQL For Loop by taking the example below. 

Let's update contact phone by using SOQL List vs SOQL For Loop.

Case 1:

SOQL List For Loop:

list<contact> conList = [Select id,phone from contact limit 1];

list<contact> conListUpdate=new list<contact>();

for(contact obj:conList){

obj.phone='111';

conListUpdate.add(obj);

}

update conListUpdate;

Case 2:

SOQL For Loop:

list<contact> conListUpdate=new list<contact>();

for(contact obj:[Select id, phone from contact limit 1]){

obj.phone='111';

conListUpdate.add(obj);

}
update conListUpdate;

Now, What is the difference between SOQL List vs SOQL For Loop ?

1) SOQL For Loop retrieves all sObjects using a call to query and queryMore whereas SOQL List For Loop retrieves a number of objects records.

2) It is advisable to use SOQL For Loop over SOQL List For Loop to avoid heap size limit error.

3) The maximum number of records that can be queried by SOQL queries is 50,000 record. 

Note: 

1) In both the above cases the maximum records that can be queried is 50,000.

2) A runtime exception is thrown if this query returns enough records to exceed your heap limit.

Total Heap Size Limit :  6 M.B Synchronous and 12 M.B Asynchronous.

In below example, let say each record is taking 2 K.B, so 50,000 will take 50,000*2=100000 K.B (100 M.B approx in conList) which will cause heap size limit error as the allowed limit is 6 M.B for synchronous.

list<contact> conList=new list<contact>();

conList=[Select id, phone from contact limit 50000]; 

To avoid the heap size error we should use SOQL For Loop as con variable highlighted using below example will have one record at a time i.e 2K.B of data at a time thus preventing heap size limit error.

for (List<Contact> con: [SELECT id, name FROM contact]){

}

Another important point to note is when querying parent-child data in SOQL For Loop we may get below error.

Error:  Aggregate query has too many rows for direct assignment.

This exception occurs when there are a large number of child records (200 or more number of records).To avoid this we should use for loop inside for loop.

Syntax:

Case 1: Will give error.....

for(Account obj:[Select id,(select id,name from contacts) from account limit 1]){

list<contact> conList=obj.contacts;  // Error here if more than 200 contacts are present

}

Case 2: Correct way of handling....

for(Account obj:[Select id,(select id,name from contacts) from account limit 1]){

 for(contact con:obj.contacts)
 {
 //some operations
 }
}