Too many SOQL queries

Question

This question has been asked before, but none of the answers on those threads have helped so far.

Getting the following error when trying to deploy a test class from the sandbox to production in the Force.com IDE:

System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, LeadPerson: execution of AfterInsert caused by: System.Exception: Too many SOQL queries: 101

LeadPerson trigger looks OK, e.g., no SOQL calls inside a loop.

trigger LeadPerson on Lead (after insert) {
    Map<Id, Lead> leadMap = new Map<Id, Lead>();
    Map<String, Lead> leadEmailMap = new Map<String, Lead>();

    for(Lead lead : Trigger.new) {
        if (lead.Email != null) {
            leadMap.put(lead.Id, lead);
            leadEmailMap.put(lead.Email, lead);
        }
    }

    Map<String, Person__c> personMap = new Map<String, Person__c>();
    for (Person__c p : [SELECT Id, Email__c FROM Person__c WHERE Email__c IN :leadEmailMap.keySet()]) {
        personMap.put(p.Email__c, p);
    }

    List<Person__c> addPersons = new List<Person__c>();
    List<PersonHistory__c> addPersonHistorys = new List<PersonHistory__c>();
    List<Lead> addPersonLead = new List<Lead>();
    Map<String, String> addPersonEmails = new Map<String, String>();

    for(Lead lead : Trigger.new) {
        if (lead.Email != null) {
            if (personMap.containsKey(lead.Email)) {
                // Create PersonHistory__c record for existing Person__c records
                Person__c getPerson = personMap.get(lead.Email);
                PersonHistory__c newPersonHistory = new PersonHistory__c();
                newPersonHistory.Person__c = getPerson.Id;
                newPersonHistory.RecType__c = 'LEAD';
                newPersonHistory.RecSubType__c = lead.inq_type__c;
                newPersonHistory.SFID__c = lead.Id;
                newPersonHistory.RecTypeStatus__c = 'NEW';
                addPersonHistorys.add(newPersonHistory);
            } else {
                // Create Person__c and PersonHistory__c record for NEW Person__c records
                String firstInitial = '';
                if (lead.FirstName != null) {
                    firstInitial = lead.FirstName.substring(0, 1);
                }

                if (!addPersonEmails.containsKey(lead.Email)) {
                    addPersonEmails.put(lead.Email, 'EMAIL');
                    Person__c newPerson = new Person__c();
                    newPerson.Email__c = lead.Email;
                    newPerson.AltKey__c = firstInitial + lead.LastName + lead.PostalCode;
                    newPerson.Current_Status__c = 'NEW';
                    addPersons.add(newPerson);
                    addPersonLead.add(lead);
                }
            }
        }
    }

    // INSERT NEW Person__c records
    List<Database.SaveResult> newPersonResult = Database.Insert(addPersons);
    for (integer i = 0; i < newPersonResult.size(); i++) {
        // Create PersonHistory__c records for new Person__c records
        PersonHistory__c newPersonHistory = new PersonHistory__c();
        newPersonHistory.Person__c = newPersonResult.get(i).getId();
        newPersonHistory.RecType__c = 'LEAD';
        newPersonHistory.RecSubType__c = addPersonLead.get(i).inq_type__c;
        newPersonHistory.SFID__c = addPersonLead.get(i).Id;
        newPersonHistory.RecTypeStatus__c = 'NEW';
        addPersonHistorys.add(newPersonHistory);
    }
    insert addPersonHistorys;
}

The debug log doesn't show a transaction having more than 5 out of 100 SOQL queries in the CUMULATIVE_LIMIT_USAGE sections. Not sure where to look next. Any help is appreciated.

Answer 1

One thing to remember with deploying to production. ALL of the testclasses have to be executed as part of the deployment. Your code looks pretty good from a SOQL standpoint, does need the DML moved outside the loop as was mentioned before), so it is probably a case where other test executions are putting you close to the limit, and your 1 soql is putting the entire transaction over the limit. Do a "Run All Tests" in production, and look at the resulting Debug log. At the very end, you should see a summary count of the SOQLs that were executed. You can then scan through the log for the SOQL summaries listed with each test that is executed, and use this to narrow down the offending code or test method.

Answer 2

When you run into a governor limit exception like Too Many SOQL Queries thrown in your code, but not directly caused by your code, look at the debug log.

The stack trace shown when an exception occurs only traces the calls within a single code unit.

A code unit is a discrete unit of work within a transaction. For example, a trigger is one unit of code, as is a webService method, or a validation rule.

So when an exception occurs in your trigger, the stack trace doesn't show what caused your trigger to be fired. But the debug log can show you the full execution path.

Suppose you wrote a trigger on Lead that creates Tasks:

trigger LeadTrigger on Lead (after insert, after update) {
    Set<String> firstNames = new Set<String>(); 
    for (Lead l: Trigger.newMap.values()) {
        if (Trigger.isInsert || Trigger.oldMap.get(l.Id).FirstName != l.FirstName || Trigger.oldMap.get(l.Id).Status != l.Status) {
            firstNames.add(l.FirstName);
        }
    }   
    if (firstNames.isEmpty()) {
        return;
    }
    Map<String, Integer> nameCounts = new Map<String, Integer>(); 
    for (AggregateResult r: [SELECT FirstName, COUNT(Id) num FROM Lead WHERE IsConverted = false AND FirstName IN :firstNames GROUP BY FirstName]) {
        nameCounts.put((String)r.get('FirstName'), (Integer)r.get('num'));
    }
    for (AggregateResult r: [SELECT FirstName, COUNT(Id) num FROM Contact WHERE FirstName IN :firstNames GROUP BY FirstName]) {
        String name = (String)r.get('FirstName');
        nameCounts.put(name, nameCounts.get(name) + (Integer)r.get('num'));
    }
    List<Task> newTasks = new List<Task>();
    for (Lead l: Trigger.new) {
        if (Trigger.isInsert) {
            newTasks.add(new Task(Subject = 'Call', WhoId = l.Id));
        }
        Integer count = nameCounts.get(l.FirstName);
        if (count > 1) {
            newTasks.add(new Task(Subject = 'Research', Description = 'Find out why there are so many people named ' + l.FirstName, WhoId = l.Id));
        }
    }
    if (!newTasks.isEmpty()) {
        insert newTasks;
    }
}

And you start working on tests for you trigger:

@IsTest
private class LeadTrigger_Test {
    private static testmethod void LeadTrigger_should_create_research_tasks() {
        List<Lead> newLeads = new List<Lead>();
        Integer leads = (Integer)[SELECT COUNT(Id) num FROM Lead][0].get('num');
        System.debug('There are ' + String.valueOf(leads) + ' leads');

        for (Integer i = 1; i <= 20; i++ ) {
            newLeads.add(new Lead(FirstName = 'Fred', LastName = 'Smith', Company = 'Acme')); 
            newLeads.add(new Lead(FirstName = 'Sally', LastName = 'Flintstone', Company = 'IBM'));
        }
        insert newLeads;
        List<Task> researchTasks = [SELECT Id FROM Task WHERE Subject = 'Research'];
        System.assert(!researchTasks.isEmpty(), 'research tasks created');
    }
}

You run your test, and you get a LimitException:

System.LimitException: Too many SOQL queries: 101
    Trigger.LeadTrigger: line 12, column 1

It's not obvious why you're getting an exception in your new trigger.

But if you look at the full execution path, it becomes obvious where to focus your attention. Execution trees and flame graphs are good ways of visualizing the execution path.

Someone else has added a poorly written trigger on Task:

trigger BadTaskTrigger on Task (after insert) {
    for (Task t: Trigger.new) {
        Integer openTasks = (Integer)[SELECT COUNT(Id) num FROM Task WHERE IsClosed = false AND WhoId = :t.WhoId GROUP By WhoId][0].get('num');
        if (openTasks > 1) {
            Lead l = new Lead(Status = 'Overloaded', Id = t.WhoId);
            update l;
        }
    }
}