Saturday, October 6, 2018

SOQL List vs SOQL For Loop

To understand the syntax of (soql for loop) and (list for loop).Let's update contact phone by using (list for loop) and (soql for loop).

Case 1)

(list for loop):

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

for(contact obj:conList){
obj.phone='111';
conListUpdate.add(obj);
}
update conListUpdate;

Case 2)

(soql for loop)

list<contact> conList=new list<contact>();
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 (list for loop) retrieves a number of objects records.

2) It is advisable to use (soql for loop) over (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 case, 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];



To avoid this we should use "SOQL for loop" as con variable highlighted below will have 1 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]){


}


Note: When querying parent-child data in (soql for loop) we may get below 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
}


Case 2) Correct way of handling....

for(Account obj:[Select id,(select id,name from contacts) from account limit 1]){
for(contact obj1:obj.contacts)
{
//some operations
}

}

4 comments:

  1. Is it advisable to write a for loop inside a for loop like you suggested in Case 2

    ReplyDelete
    Replies
    1. https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_loops_for_SOQL.htm

      Delete
  2. Yes, we can use For-inside-For Loop. However, it is not advisable to write a SOQL query inside a FOR-loop. Hope it Helps!

    ReplyDelete
  3. even though, I am using the SOQL for loop, I am getting Too many query rows

    Note: i have 2L Records in a object


    Please suggest how can i proceed to get all the records
    I dont want to go for batch class

    ReplyDelete