How to avoid ISOTrigger: System.LimitException: Too many SOQL queries: 101

Question

I received the following error:

Error: Invalid Data. Review all error messages below to correct your data. Apex trigger ISOTrigger caused an unexpected exception, contact your administrator: ISOTrigger: System.LimitException: Too many SOQL queries: 101

In the debug log I've found the following:

15:11:56.0 (762902111)|SOQL_EXECUTE_BEGIN|[94]|Aggregations:0|SELECT Id, OwnerId, IsRecurrence, RecurrenceActivityId FROM Task WHERE (IsClosed = FALSE AND (IsRecurrence = TRUE OR RecurrenceActivityId = '') AND AccountId = :tmpVar1)
15:11:56.0 (762942801)|EXCEPTION_THROWN|[94]|System.LimitException: Too many SOQL queries: 101
15:11:56.0 (763220286)|FATAL_ERROR|System.LimitException: Too many SOQL queries: 101

Class.AccountHandler.beforeUpdate: line 94, column 1
Class.TriggerFactory.execute: line 95, column 1
Class.TriggerFactory.createHandler: line 29, column 1
Trigger.AccountTrigger: line 3, column 1

15:11:56.0 (763234604)|FATAL_ERROR|System.LimitException: Too many SOQL queries: 101

Here is the code in method beforeUpdate and AccountHandler class:

public void beforeUpdate(SObject oldSo, SObject so) {

   for(SObject bu :Trigger.new) {
       Account a = (Account) bu;

       List<Task> tasksFromDatabase = new List<Task> ([SELECT Id, OwnerId, IsRecurrence, RecurrenceActivityId FROM Task WHERE IsClosed=false AND (IsRecurrence = true OR RecurrenceActivityId = '') AND AccountId = :a.Id]);

       String agentName = a.Agents__c;
       String recruiterName = a.Recruiter__c;//TO DO ::: delete, not used

       if(agentName != null) {
           List<ISOs__c>isoName = [SELECT Name, Recruiter_ID_num__c, Active__c FROM ISOs__c WHERE Id IN (SELECT ISO_Company__c FROM Agent__c WHERE Id = :agentName)];
           if(isoName.size() > 0) {
               if(isoName[0].Active__c == false) {
                   a.ISO_Name__c.addError('The selected agent is linked to an inactive Iso');
               }

           a.ISO_Name__c = isoName[0].Name;
           a.ISO_recruiter_id__c = IsoName[0].Recruiter_ID_num__c;
           }
       } else if(agentName == null) {
           a.ISO_Name__c = '';
           a.ISO_recruiter_id__c = '';
       }

       for(SObject sob :Trigger.new) {
           Account ab = (Account) sob;
           Account oldAB = (Account)Trigger.oldMap.get(ab.id);
           if (ab.Status__c != null && oldAB.Status__c!=null) {       
               if(ab.Status__c != oldAB.Status__c) {   
                   statusmail mail = new statusmail();
                   mail.setAccountInfo(ab);
                   mail.SendAccountEmail();            
                   if(ab.Agents__c != null)
                       mail.sendAgentEmail();
                   if(ab.Recruiter__c != null)
                       mail.sendRecruiterEmail();
               }
           }
       }       
    }
}

Please advise how to avoid this error?

Answer 1

Bulkification 101

This is the most basic pattern to bulkify code. You need to build your entire coding paradigm around collections, not individual records. You should query the records you need before your loop and cache them in a Map for retrieval later on.

Child Records

It looks like all of your queries are for child data, so this is the basic principle you need to apply.

Incorrect Bulkification

for (/*loop definition*/)
{
    List<ChildObject__c> childRecords = [/*query*/];
}

Correct Bulkification

Map<Id, List<ChildObject__c>> parentToChildren = new Map<Id, List<ChildObject__c>>();
for (ChildObject__c child : [/*child query*/])
{
    if (!parentToChildren.containsKey(child.Parent__c))
        parentToChildren.put(child.Parent__c, new List<ChildObject__c>());
    parentToChildren.get(child.Parent__c).add(child);
}

for (/*original loop definition*/)
{
    List<ChildObject__c> children = parentToChildren.get(loopRecord.Id);
}

Parent Records

The pattern for parent records is similar, and you should familiarize yourself with the idea here as well.

Incorrect Bulkification

for (/*loop definition*/)
{
    ParentObject__c parent = [/*query*/];
}

Correct Bulkification

Map<Id, ParentObject__c> parents = new Map<Id, ParentObject__c>([/*parent query*/]);
// if you need to map parents by Id, you can leverage the built in constructor

for (/*original loop definition*/)
{
    ParentObject__c parent = parents.get(loopRecord.ParentId);
}

Answer 2

You have two queries in a for loop. Here's the solution if you want to remove the first query from the for loop. You have to do the same for ISOs__c query.

public void beforeUpdate(SObject oldSo, SObject so) {

   List<Id> accountIds = new List<Id>();
   for(SObject bu :Trigger.new) {
       Account a = (Account) bu;
       accountIds.add(a.Id);
   }

   Map<Id, List<Task>> tasksByAccountId = new Map<Id, List<Task>>();
   for(Task task : [SELECT Id, OwnerId, IsRecurrence, RecurrenceActivityId, AccountId FROM Task WHERE IsClosed=false AND (IsRecurrence = true OR RecurrenceActivityId = '') AND AccountId in :accountIds]){
      if(tasksByAccountId.containsKey(task.AccountId)){
         tasksByAccountId.get(task.AccountId).add(task);
      }else{
         List<Task> tasks = new List<Task>(){task};
         taskByAccountId.put(task.AccountId, tasks);
      }
   }

   for(SObject bu :Trigger.new) {
       Account a = (Account) bu;

       List<Task> tasksFromDatabase = taskByAccountId.get(a.Id);