Exafort

APEX code nuggets – How to avoid executing SOQL or DML in loops

This is a common mistake where queries or DML statements are placed inside a for loop to lookup a record once per iteration. By doing this you will very quickly hit the governor limit of SOQL queries and/or DML statements (insert, update, delete, undelete).

Instead, move any database operations outside of for loops. Form a single query such that you get all the required records at once. You can then iterate over the results. If you need to modify the data, batch update into a list and invoke your DML once for that list of data.

Here is an example showing both a query and a DML statement 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) {         
      //THE 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 100 records,
      //the trigger will exceed the trigger governor limit of maximum
      //100 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 150
         //records, the trigger will exceed the trigger governor limit
         //of 150 DML Operations maximum.                                   
         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. An individual Apex request gets a maximum of 100 SOQL queries before exceeding that governor limit. So if this trigger is invoked by a batch of more than 100 Account records, the governor limit will throw a runtime exception.

 
 
 

In this example, because there is a limit of 150 DML operations per request, a governor limit will be exceeded after the 150th contact is updated.

 
 
 

Here is the optimal way 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) {
  //In this case we are using the child relationships to filter down
  //and form a single query to get the required records.
  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 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 one SOQL query and one update statement is executed.

 
Exafort
Privacy Overview

This website uses cookies so that we can provide you with the best user experience possible. Cookie information is stored in your browser and performs functions such as recognising you when you return to our website and helping our team to understand which sections of the website you find most interesting and useful.