Trouble with converting leads with triggers getting 101 queries

Question

I have a set of business rules for assigning leads to the correct salesperson that runs in a before trigger. Then in an after trigger if a field is filled in auto convert the lead. The trigger makes a callout to a @future function that will create a custom object. The problem I am running into is this generates a 101 query error and I don't understand how? Thanks for your help.

The exact error message is:

LeadTriggerAfter: execution of AfterUpdate caused by: System.DmlException: ConvertLead failed. First exception on row 8;

first error: UNKNOWN_EXCEPTION, System.LimitException: Too many SOQL queries: 101

Trigger.LeadTriggerBefore: line 7, column 1: []

Trigger.LeadTriggerAfter: line 73, column 1

Before trigger:

trigger LeadTriggerBefore on Lead (before insert, before update) {

    list<string> emailSuffix = new list<string>();
    list<string> companyName = new list<string>();

    GroupMember varOwner = [SELECT UserOrGroupId FROM GroupMember WHERE Group.name = 'VarOwner' limit 1];
    List<GroupMember>facilityOwner = [SELECT UserOrGroupId FROM GroupMember WHERE Group.name = 'Facility Owners'];
    List<GroupMember>consultant = [SELECT UserOrGroupId FROM GroupMember WHERE Group.name = 'Consultant'];

    map<id,GroupMember> AllSales = new map<id, GroupMember>();
    List<id> SalesList = new List<id>();
    for(GroupMember g : facilityOwner)
    {
        AllSales.put(g.UserOrGroupId,g);
        SalesList.add(g.UserOrgroupId);
    }
    for(GroupMember g : consultant)
    {
        AllSales.put(g.UserOrGroupId,g);
        SalesList.add(g.UserOrgroupId);
    }

    for(Lead newLead : Trigger.new)
    {
        if( newLead.emailSuffix__c != null &&
            string.isNotBlank(newLead.emailSuffix__c))
        {
            emailSuffix.add(newLead.emailSuffix__C);
        }
        if(string.isNotBlank(newLead.company))
        {
            companyName.add(newLead.company);
        }
    }

    List<contact> matchingBoth = [SELECT ownerID, account.name, emailSuffix__c FROM contact WHERE account.name in :companyName and emailSuffix__c in:emailSuffix  ];
    List<contact> matchingName = [SELECT ownerID, account.name FROM contact WHERE account.name in:companyName  ];

    for(Lead myLead : Trigger.new)
    {
        boolean matched = false;

        if(string.isNotBlank(myLead.VAR_Owner__c))
        {
            myLead.OwnerId = varOwner.UserOrGroupId;
            matched = true;
        }
        for(contact c : matchingBoth)
        {
            if(c.account.name.equals(myLead.company) && c.emailSuffix__c.equals(myLead.emailSuffix__c))
            {
                matched = true;
                myLead.ownerId = c.ownerId;
            }
        }
        if(!matched)
        {
            for(contact c: matchingName)
            {
                if(c.account.name.equals(myLead.company))
                {
                    matched = true;
                    myLead.ownerId = c.ownerId;
                }
            }
        }
        Integer random = 0;
        if(!matched)
        {
            if(!AllSales.containsKey(myLead.OwnerId))
            {
                if(string.isBlank(myLead.Industry))
                {
                    random = Math.round(Math.random() * (AllSales.size() -1 ));
                    myLead.OwnerId = SalesList[random];
                }
                else if(myLead.Industry.equals('facility owner'))
                {
                    random = Math.round(Math.random() * (facilityOwner.size() -1 ));
                    myLead.ownerId = facilityOwner[random].UserOrGroupId;
                }
                else if(myLead.Industry.equals('consultant'))
                {
                    random = Math.round(Math.random() * (consultant.size() -1 ));
                    myLead.ownerId = facilityOwner[random].UserOrGroupId;
                }
            }
        }
    }
}

After trigger:

trigger LeadTriggerAfter on Lead (after insert, after update) {

    LeadStatus convertStatus = [SELECT Id, MasterLabel FROM LeadStatus WHERE IsConverted=true LIMIT 1];
    list<string> companies = new list<string>();
    List<Database.LeadConvert> leadConverts = new List<Database.LeadConvert>();
    map<id,List<string>> leadAuth = new map<id,List<string>>();    

    for(Lead newLead : Trigger.new)
    {
        if(String.isNotBlank(newLead.Company))
        {
            companies.add(newLead.Company);
        }
    }
    List<Account> mergeAccount = [SELECT id, name FROM Account WHERE name in :companies];



    for(Lead newLead : Trigger.new)
    {
        string demoType = '';
        string serialAuthCode = '';
        // other fields omitted 
        if(String.isNotBlank(newLead.customField) )
        {
            demoType = 'customField';
            serialAuthCode = newLead.customField;
        }
        if(String.isNotBlank(demoType))
        {
            if(!newLead.IsConverted)
            {
                System.debug('Called convert lead');
                System.debug('convert lead ' + newLead.id + ' demoType ' + demoType + ' authCode' + serialAuthCode);

                Database.LeadConvert lc = new Database.LeadConvert();
                lc.setLeadId(newLead.Id);

                lc.setConvertedStatus(convertStatus.MasterLabel);

                for(Account company: mergeAccount)
                {
                    if(newLead.Company.equals(company.name))
                    {
                        lc.setAccountId(company.Id);
                    }
                }

                lc.setDoNotCreateOpportunity(true);
                leadConverts.add(lc);                
            }
        }
    }
    if(!leadConverts.isEmpty()){
        List<Database.LeadConvertResult> lcr = Database.convertLead(leadConverts);
    }
}

Answer 1

This code is probably not the primary culprit. I'm guessing you have other triggers in the system that are contributing to the overall problem. However, there are things you could do to reduce the problem. This mostly has to do with "caching", lazy loading, and reducing redundant calls.

Caching

To cache a record means you don't have to query it again that transaction. Here, since we're dealing with recursive triggers, we need a way to make sure that we don't query the same records over and over again. You have at least three queries that could be resolved this way:

GroupMember varOwner = [SELECT UserOrGroupId FROM GroupMember WHERE Group.name = 'VarOwner' limit 1];

Could become:

public class RecordCache {
    public static GroupMember varOwner = [SELECT UserOrGroupId FROM GroupMember WHERE Group.name = 'VarOwner' limit 1];
}

Now, you can reference it in your trigger as:

myLead.OwnerId = RecordCache.varOwner.UserOrGroupId;

This is a static variable, so it will only be initialized once. Unfortunately, you might not need to query the record at all, so we can employ lazy loading.

Lazy Loading

This is a technique where we wait until we actually need a resource before we query it. If we don't end up using it, it saves those governor limits for other purposes.

public class RecordCache {
    public static GroupMember varOwner { get {
        if(varOwner == null) {
            varOwner = [SELECT UserOrGroupId FROM GroupMember WHERE Group.name = 'VarOwner' limit 1];
        }
        return varOwner;
    } }
}

Now, if varOwner is never accessed during a transaction, it will never waste the query.

Reducing Redundancy

Since these triggers can call themselves recursively, you should probably limit how many times each record will be processed per transaction. We can employ static variables again to limit the call to just a single use per transaction. First, we need a place to store some ID values:

public class RecordCache {
    public static Set<Id> processedRecords = new Set<Id>();
}

From there, we then need to just update the trigger:

trigger LeadAfterTrigger on Lead (after insert, after update) {
    if(RecordCache.processedRecords.containsAll(Trigger.newMap.keySet()) {
        return;
    }
    RecordCache.processedRecords.addAll(Trigger.newMap.keySet());
    // Other logic here
}

You'll probably want one variable per trigger, and possibly consider adding the values to the map only if you perform the intended logic. You'll need to consider the recursive factors carefully here.

Also keep in mind that all queries from the entire conversion process count against you: Lead, Contact, Account, and possibly opportunity triggers will affect your total transaction.

Your best friend here will be the debug logs. Turn your profiling, apex code, and database trace levels up to FINEST, and run your transactions until it fails again. Check to see how many times each query and function is being called. Those logs will show you which queries you should consider caching, lazy loading, and/or skipping redundant calls on.