Person Writing

Exafort Blog

WE'VE GOT GREAT IDEAS TO SHARE!

 
Search
  • 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.


24 views0 comments