Apex resource warnings

Question

I'm using the following custom controller convertLeadToMerchant to convert Lead to Account object but receive the following warnings:

Operation: /apex/convertLeadToMerchant

By user/organization: 0051400000Bg7Vx/00D56000000CroQ

Caused the following Apex resource warnings:

Number of SOQL queries: 70 out of 100

The code for convertLeadToMerchant.apxc is here:

public with sharing class LeadToMerchantController {
    Lead objLead;
    String objLeadId;
    public LeadToMerchantController(ApexPages.StandardController controller) {
        objLead = (Lead) controller.getRecord();
    }

    // Remove all extensions from the company name
    // The extensions from custom object Company_Extensions__c are loaded in Set<String> companyExtensions
    // Company can be with upper and lower case
    // Company is made lowercase
    // That is why company and companyNameLowerCase are processed in the same way
    // And the method returns the processed company => company without its extensions
    public static String splitCompanyName(String company) {
        String companyNameLowerCase = company.toLowerCase();
        List < Company_Extensions__c > extensions = Company_Extensions__c.getAll().values();
        Set < String > companyExtensions = new Set < String > ();
        for (Company_Extensions__c extension: extensions) {
            companyExtensions.add(extension.Name);
            for (String companyExtension: companyExtensions) {
                integer extIndex = companyNameLowerCase.indexOf(' ' + companyExtension);
                if (extIndex > -1) {
                    company = company.substring(0, extIndex);
                    companyNameLowerCase = companyNameLowerCase.substring(0, extIndex);
                }
            }
        }
        return company;
    }
    // website parameter contains list of one or more url addresses separeated by specific separator
    // the urls are split by separator and each website is processed in the following way
    // 1.The port in the website is removed
    // 2.The www are added to the website
    // 3.The directory index (.../index.html) is removed
    // 4.The double slashes are removed 
    public static String normalizeWebsite(String website) {
        String normWebsite = '';
        if (website != null && !website.equals('')) {
            List < String > leadSplitWebsitesList = website.split('[,;]');
            String websiteSeparator = getUrlSeparator(website);
            for (String leadSplitUrls: leadSplitWebsitesList) {
                leadSplitUrls = leadSplitUrls.toLowerCase();
                leadSplitUrls = NormalizationUtils.removePort(leadSplitUrls);
                leadSplitUrls = NormalizationUtils.addWWW(leadSplitUrls);
                leadSplitUrls = NormalizationUtils.removeDirectoryIndex(leadSplitUrls);
                leadSplitUrls = NormalizationUtils.removeDoubleSlashes(leadSplitUrls);
                normWebsite += leadSplitUrls;
                normWebsite += websiteSeparator;
            }
            if (!websiteSeparator.equals('') && normWebsite.endsWith(websiteSeparator)) {
                // remove last symbol
                normWebsite = normWebsite.substring(0, normWebsite.length() - 1);
            }
        }
        return normWebsite;
    }
    // Return separator of the urls
    // Return '' if the urls is empty or if the separator is not among the symbol ',' symbol  ';' sybmol ':' 
    public static string getUrlSeparator(String urls) {
        if (urls == null || urls.equals('')) {
            return '';
        }
        integer urlSeparator = urls.indexOf(',');
        if (urlSeparator > -1) {
            return ',';
        }
        urlSeparator = urls.indexOf(';');
        if (urlSeparator > -1) {
            return ';';
        }
        return '';
    }
    // returns the mail account name without the domain
    public static String normalizeEmail(String email) {
        String normalizedEmail = '';
        normalizedEmail = normalizedEmail.toLowerCase();
        if (normalizedEmail != null && !normalizedEmail.equals('')) {
            Integer atIndex = email.indexOf('@');
            if (atIndex > -1) {
                normalizedEmail = email.substring(0, atIndex);
            } else {
                normalizedEmail = email;
            }
        }
        return normalizedEmail;
    }
    // finds all real url addresses in the record parameter
    // all found url adress are added into a list and the method returns the list
    public static List < String > extractWebsites(String record) {
        List < String > patternWebsite = new List < String > ();
        if (record == null || record.equals('')) {
            return patternWebsite;
        }
        String regexEncoding = '(https?://www.|https?|www.)([A-Za-z\\d._~:/?#\\[\\]@!$&\'()*+,;=-]|%[0-9a-fA-F]{2})+[.][A-Za-z]{2,3}';
        Matcher websiteMatcher = Pattern.compile(regexEncoding).matcher(record);
        while (websiteMatcher.find()) {
            // the matching value
            String entireUrl = websiteMatcher.group();
            if (entireUrl != null && !entireUrl.equals('')) {
                patternWebsite.add(entireUrl);
            }
        }
        return patternWebsite;
    }
    // Set status Disqualified - Dead to the lead if  
    // 1. Lead company name coincide with Account company name; 
    // OR
    // 2. Lead registration number coincide with Account registration number;
    // OR
    // 3. Some of the Lead websites coincide with some of the Account websites;
    // OR
    // 4. Some of the Lead emails coincide with some of the Account emails;
    // OR
    // 5. If merchant status is one of the following In Review/Active/Prospect by EMP;
    public String leadStatusDisqualifiedDead(Lead leadObj) {
        String company = leadObj.Company;
        if (company != null && !company.equals('')) {
            company = splitCompanyName(company);
            List < Account > accountCompanyList = [SELECT Name, id, Status__c FROM Account];
            List < Lead > updateLeadList = new List < Lead > ();
            for (Account acc: accountCompanyList) {
                String accountCompany = acc.Name;
                accountCompany = splitCompanyName(accountCompany);
                if (accountCompany != null) {
                    if (accountCompany.equals(company)) {
                        String accountStatus = acc.Status__c;
                        //if there is no account status do not stop the convertion 
                        if (accountStatus != null && !accountStatus.equals('')) {
                            if (accountStatus.equals('In Review') || accountStatus.equals('Active') || accountStatus.equals('Prospect')) {
                                updateLeadStatus(leadObj);
                                String accountId = acc.Id;
                                System.debug('1. Lead status is set to Disqualified - Dead');
                                return accountId;
                            }
                        }
                    }
                }
            }
        }
        String regNumber = leadObj.Registration_Number__c;
        if (regNumber != null && !regNumber.equals('')) {
            List < Account > accountRegNumberList = [SELECT Registration_Number__c, Status__c FROM Account];
            List < Lead > updateLeadList = new List < Lead > ();
            for (Account accRegNumber: accountRegNumberList) {
                String accountRegistrationNumber = accRegNumber.Registration_Number__c;
                if (accountRegistrationNumber != null) {
                    if (accountRegistrationNumber.equals(regNumber)) {
                        // Lead registration number coincides with Account registration number
                        String accountStatus = accRegNumber.Status__c;
                        if (accountStatus != null && !accountStatus.equals('')) {
                            if (accountStatus.equals('In Review') || accountStatus.equals('Active') || accountStatus.equals('Prospect')) {
                                updateLeadStatus(leadObj);
                                String accountId = accRegNumber.Id;
                                System.debug('2. Lead status is set to Disqualified - Dead');
                                return accountId;
                            }
                        }
                    }
                }
            }
        }
        String website = leadObj.Website;
        if (website != null && !website.equals('')) {
            List < String > leadWebsites = extractWebsites(website);
            for (String leadUrl: leadWebsites) {
                String normWebsite = normalizeWebsite(leadUrl);
                String leadWebsite = normWebsite.trim();
                List < String > leadSplitWebsitesList = leadWebsite.split('[,;\\s]');
                List < Account > accountWebsiteList = [SELECT Website, Status__c FROM Account];
                List < Lead > updateLeadListURL = new List < Lead > ();
                if (accountWebsiteList != null) {
                    for (Account acc: accountWebsiteList) {
                        String accountWebsite = acc.Website;
                        List < String > extractedAccountWebsite = extractWebsites(accountWebsite);
                        for (String accWebsite: extractedAccountWebsite) {
                            String accountSite = normalizeWebsite(accWebsite);
                            String accountStatus = acc.Status__c;
                            if (accountSite != null) {
                                for (String leadSplitUrls: leadSplitWebsitesList) {
                                    if (accountSite.equals(leadSplitUrls)) {
                                        if (accountStatus.equals('In Review') || accountStatus.equals('Active') || accountStatus.equals('Prospect')) {
                                            updateLeadStatus(leadObj);
                                            String accountId = acc.Id;
                                            System.debug('3. Lead status is set to Disqualified - Dead');
                                            return accountId;
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
        List < Contact > leadContacts = [select id, company_name__c, email from contact where company_name__c =: leadObj.id];
        List < Account > accountList = [SELECT name, id from Account];
        if (accountList.size() > 0) {
            for (Account objAccount: accountList) {
                List < Merchant_To_Contact__c > accountContacts = [select id, Merchant_Name__c, email__c from Merchant_To_Contact__c where Merchant_Name__c =: objAccount.id];
                Integer leadContactsSize = leadContacts.size();
                Integer accountContactsSize = accountContacts.size();
                if (leadContactsSize == accountContactsSize && leadContactsSize > 0) {
                    // compare the elements of the lists only if their size is the same
                    for (Contact ldContact: leadContacts) {
                        String leadEmail = ldContact.Email;
                        leadEmail = normalizeEmail(leadEmail);
                        for (Merchant_To_Contact__c merchantToContact: accountContacts) {
                            String merchantEmail = merchantToContact.Email__c;
                            merchantEmail = normalizeEmail(merchantEmail);
                            if (merchantEmail.equals(leadEmail)) {
                                List < Account > accountStatus = [SELECT Status__c FROM Account where id =: objAccount.id];
                                String accountId = merchantToContact.Merchant_Name__c;
                                if (accountStatus.size() > 0) {
                                    if (accountStatus.equals('In Review') || accountStatus.equals('Active') || accountStatus.equals('Prospect')) {
                                        updateLeadStatus(leadObj);
                                        System.debug('4. Lead status is set to Disqualified - Dead');
                                        // if any of the emails for lead contacts coincide with any of the emails for the account contacts return true
                                        return accountId;
                                    }
                                } else {
                                    return accountId;
                                }
                            }
                        }
                    }
                }
            }
        }
        String accountId = accWithOpenOpp();
        if (accountId != null && !accountId.equals('')) {
            updateLeadStatus(leadObj);
            System.debug('5. Lead status is set to Disqualified - Dead');
            // if there is an existing account by opportunity the process of conversion is stopped
            return accountId;
        }
        // Lead should be converted to merchant
        return '';
    }
    // Check for already existing account by the following criterium
    // 1. Account status is 'Terminated' or 'Rejected' or 'Prospect'
    // 2. there is at least one Opportunity which is open and with type 'New Business'
    private String accWithOpenOpp() {
        List < Account > allAccounts = [SELECT status__c from Account];
        for (Account acc: allAccounts) {
            String accStatus = acc.Status__c;
            if (accStatus != null && !accStatus.equals('')) {
                if (accStatus.equals('In Review') || accStatus.equals('Active') || accStatus.equals('Prospect')) {
                    List < Opportunity > opp = [SELECT IsClosed, Type from Opportunity WHERE AccountId =: acc.Id];
                    for (Opportunity opportunity: opp) {
                        if (opportunity != null && opportunity.type != null) {
                            if (opportunity.type.equals('New Business') && !opportunity.isClosed) {
                                String accountId = acc.Id;
                                return accountId;
                            }
                        }
                    }
                }
            }
        }
        return '';
    }
    private void updateLeadStatus(Lead leadObj) {
        leadObj.Status = 'Disqualified – Dead';
        leadObj.Status_Reason__c = 'Duplicate Lead';
        update leadObj;
    }
    public PageReference convertOrRedirect() {
        String convertToMerchant = '/lead/leadconvert.jsp?retURL=%2F' + objLead.id + '&id=' + objLead.id + '&nooppti=0&nooverride=1';
        PageReference pageRef = new PageReference(convertToMerchant);
        pageRef.setRedirect(True);
        List < Lead > leadList = [SELECT name, id, company, website, Registration_Number__c from Lead where id =: objLead.id];
        if (leadList.size() > 0) {
            objLead = leadList.get(0);
            String objLeadId = objLead.id;
            String accountId = leadStatusDisqualifiedDead(objLead);
            if (accountId == null || accountId.equals('')) {
                System.debug('pageRef ' + pageRef);
                return pageRef;
            } else {
                String doNotConvertToMerchant = '/apex/convertToMerchant';
                pageRef = new PageReference(doNotConvertToMerchant);
                pageRef.getParameters().put('LeadId', objLeadId);
                pageRef.getParameters().put('AccId', accountId);
                pageRef.setRedirect(True);
                return pageRef;
            }
        }
        return pageRef;
    }
}

Could you please advise how to avoid the warinings?

Answer 1

So, here's an advise how to avoid these sort of warnings:

Bulkify your code. Right now you have multiple SOQL queries that are inside loops. And even a SOQL query that's inside a loop that's inside a loop. Which is also inside a loop! Talking about this one:

for (Account objAccount: accountList) {
    // some code
        for (Contact ldContact: leadContacts) {
            // some more code
            for (Merchant_To_Contact__c merchantToContact: accountContacts) {
                    // QUERY!
                    List < Account > accountStatus = [SELECT Status__c FROM Account where id =: objAccount.id];

Now imagine that there are 5 account records, 5 contact records and 5 merchant_to_contact records. In this case, this query will fire 5*5*5=125 times, which is 25 queries more than an allowed limit.

So, basically the advise is: do not query inside the loops!

EDIT

As Mr.Frodo asked in his comment, I'll put a little example.

Imagine that you have a trigger that sets a value to a field of a record that's a lookup of another record. You could do the following:

public static void afterInsert(List<CustomObject1__c> co1List) {
    for (CustomObject1__c co1: co1List) {
        if (co1.co2__c != null) {
            CustomObject2__c co2 = [SELECT Id, co1IsLinked__c FROM CustomObject2__c WHERE Id = :co1.co2__c];
            co2.co1IsLinked__c = true;
            update co2;
        }
    }
}

However, here we have a query and a DML inside a loop, which is not a best practice(and is actually a bad practice). If you insert in a bulk, let's say 101 records of CustomObject1__c which all have co2__c lookup filled in, the query will have to be executed 101 times, but the limit is only 100, so after 100 it will fail.

In order to fix it and make sure it works for multiple records(bulkify), we should move the SOQL query and the DML out of the loop:

public static void afterInsert(List<CustomObject1__c> co1List) {
    List<Id> co2Ids = new List<Id>();
    for (CustomObject1__c co1: co1List) {
        if (co1.co2__c != null) {
            co2Ids.add(co1.co2__c);
        }
    }
    if (!co2Ids.isEmpty()) {
        List<CustomObject2__c> co2List = [SELECT Id, co1IsLinked__c FROM CustomObject2__c WHERE Id IN :co2Ids];
        List<CustomObject2__c> co2ToUpdate = new List<CustomObject2__c>();
        for (CustomObject2__c co2: co2List) {
            co2.co1IsLinked__c = true;
            co2ToUpdate.add(co2);
        }
        if (!co2ToUpdate.isEmpty()) {
            update co2ToUpdate;
        }
    }
}

In this case, the query will only be executed once and only if at least one CustomObject1__c record had a co2__c lookup filled in. Now, even if you insert 200 records at once, the query will only be executed once.

The same goes for the DML: it will only be executed once for all affected records.

The above can also be reduced to the follow which removes the need for a query and the second loop altogether.

public static void afterInsert(List<CustomObject1__c> co1List) {
    Map<Id,CustomObject2__c> co2ToUpdate = new Map<Id, CustomObject2__c>();

    for (CustomObject1__c co1: co1List) {
        if (co1.co2__c != null) {
            //In this case no need to check map for existing as any overwrites would be setting the same values
            co2ToUpdate.put(
                co1.co2__c,
                New CustomObject2__c(
                    Id = co1.co2__c,
                    co1IsLinked__c = true
                )
            );
        }
    }

    update co2ToUpdate.values(); //no need to check for empty
   }