Best Practice: Avoid SOQL Queries Inside FOR Loops

Short Description of Advice

Rewrite code that executes queries inside for loops by moving the query outside of the loop and extending it to query all necessary data.

Motivation for Advice

By moving queries outside of for loops, your code will run faster, and is less likely to exceed governor limits. A common mistake is that queries are placed inside a for loop. There is a governor limit that enforces a maximum number of SOQL queries. When queries are placed inside a for loop, a query is executed on each iteration and governor limit is easily reached. Instead, move the SOQL query outside of the for loop and retrieve all the necessary data in a single query.

Here is an example of a having a query inside a for loop:

trigger accountTestTrggr on Account (before insert, before update) {
	  
      //For loop to iterate through all the incoming Account records
      for(Account a: Trigger.new){
      	  /*
      	    THIS FOLLOWING QUERY IS INEFFICIENT AND DOESN'T SCALE
      	    Since the SOQL Query for related Contacts is within the FOR loop, if this trigger is initiated 
      	    with more than 20 records, the trigger will exceed the trigger governor limit
            of maximum 20 SOQL Queries.
      	  */
      	  List<Contact> contacts = [select id, salutation, firstname, lastname, email 
                        from Contact where accountId = :a.Id];
 		  
 	  for(Contact c: contacts){
 	  	  System.debug('Contact Id[' + c.Id + '], FirstName[' + c.firstname + '], 
                                         LastName[' + c.lastname +']');
 	  	  c.Description=c.salutation + ' ' + c.firstName + ' ' + c.lastname;
 	  	   /*
      	             THIS FOLLOWING DML STATEMENT IS INEFFICIENT AND DOESN'T SCALE
      	             Since the UPDATE dml operation is within the FOR loop, if this trigger is initiated 
      	             with more than 20 records, the trigger will exceed the trigger governor limit 
                     of maximum 20 DML Operations.
      	            */
      	      
 		  update c;
 	  }    	  
      }
}

Since there is a SOQL query within the for loop that iterates across all the Account objects that initiated this trigger, a query will be executed for each Account. And by default, a trigger gets a maximum of 20 SOQL queries before exceeding that governor limit. So if this trigger is invoked by a batch of more than 20 Account records, the governor limit will throw a runtime exception.

Keep in mind that the same is true for DML operations as well. Meaning, try to avoid having DML operations (insert, update, delete) inside a for Loop since that will also unnecessarily exceed the governor limit pertaining to DML operations. In this example, a trigger only allows 20 DML operations so a governor limit will be exceeded after the 20th Contact is updated.

Here is the optimal way to 'bulkify' the code to efficiently query the Contacts in a single query and only perform a single update DML operation.

trigger accountTestTrggr on Account (before insert, before update) {
  //This queries all Contacts related to the incoming Account records in a single SOQL query.
  //This is also an example of how to use child relationships in SOQL
  List<Account> accountsWithContacts = [select id, name, (select id, salutation, description, 
                                                                firstname, lastname, email from Contacts) 
                                                                from Account where Id IN :Trigger.newMap.keySet()];
	  
  List<Contact> contactsToUpdate = new List<Contact>{};
  // For loop to iterate through all the queried Account records 
  for(Account a: accountsWithContacts){
     // Use the child relationships dot syntax to access the related Contacts
     for(Contact c: a.Contacts){
   	  System.debug('Contact Id[' + c.Id + '], FirstName[' + c.firstname + '], LastName[' + c.lastname +']');
   	  c.Description=c.salutation + ' ' + c.firstName + ' ' + c.lastname; 
   	  contactsToUpdate.add(c);
     }    	  
   }
      
   //Now outside the FOR Loop, perform a single Update DML statement. 
   update contactsToUpdate;
}

Now if this trigger is invoked with a single Account record or up to 200 Account records, only 1 SOQL query and 1 update statement is executed.

Resources

Author: Andrew Albert (2009-07-16)

Categories: Logic, Database