Apex Code Best Practices Part-II

Best Practice #2: Avoid SOQL Queries or DML statements inside FOR Loops

The previous Best Practice talked about the importance of handling all incoming records in a bulk manner. That example showed use of a for loop to iterate over all of the records in the Trigger.new collection. A common mistake is that queries or DML statements are placed inside a for loop. There is a governor limit that enforces a maximum number of SOQL queries. There is another that enforces a maximum number of DML statements (insert, update, delete, undelete). When these operations are placed inside a for loop, database operations are invoked once per iteration of the loop making it very easy to reach these governor limits.

Instead, move any database operations outside of for loops. If you need to query, query once, retrieve all the necessary data in a single query, then iterate over the results. If you need to modify the data, batch up data into a list and invoke your DML once on 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) {
      	  
      //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 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 ‘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 one SOQL query and one update statement is executed.

---