Apex CPU time limit exceeded error in trigger after insert

Question

I have a trigger on lead after insert that query all accounts and all contacts created the past 30 days to compare 4 fields (external id and legal number on accounts / mobile and email on contacts) and automatically convert the lead if a match is found.

I'm a beginner in APEX, even so I've tried to avoid nested queries and other traps thanks to answers from the community 1 year ago, I feel that my code is still not optimized. Indeed, it works most of the time but I still reach limits sometimes.

If you have any clues to help me here, it would be really appreciated !

trigger LeadDeduplicate2 on Lead (after insert) {

    
    List<Account> accounts = [SELECT ID, Account_ID__c, Siret__c, OwnerId FROM Account WHERE Account_ID__c != NULL AND CreatedDate = LAST_N_DAYS:1];
    List<Contact> contacts = [SELECT ID, Email, MobilePhone, AccountId, OwnerId FROM Contact WHERE CreatedDate = LAST_N_DAYS:30];

    Map<String, Account> m1 = new Map<String, Account>();
    Map<String, Account> m2 = new Map<String, Account>();
    Map<String, Contact> m3 = new Map<String, Contact>();
    Map<String, Contact> m4 = new Map<String, Contact>();
    
    for(Account myAccount :accounts){
        if(myAccount.Account_ID__c != null) {
            m1.put(myAccount.Account_ID__c, myAccount);   
        }
        if(myAccount.Siret__c != null) {
            m2.put(myAccount.Siret__c, myAccount);
        }
    }
    
    for(Contact myContact :contacts){
        If(myContact.MobilePhone != null && myContact.MobilePhone != 'null'){
            String phoneWithoutCountryCode = Utils.mobilePhoneWithoutCodeCountry(myContact.MobilePhone);
            m4.put(phoneWithoutCountryCode, myContact);
        }
        if(myContact.Email != null) {
            m3.put(myContact.Email, myContact);   
        }
        System.debug(Limits.getCPUTime()); 
    }
    
    for(Lead lead : trigger.new) {
        if(lead.IsExternal__c) {
            if(m1.containsKey(lead.Account_ID__c)){
                Account account = m1.get(lead.Account_ID__c);
                Utils.mergeLeadWithAccount(lead, account);
                break;
            }
            
            if(m2.containsKey(lead.Siret__c)){
                Account account = m2.get(lead.Siret__c);
                Utils.mergeLeadWithAccount(lead, account);
                break;
            }
            
            if(m3.containsKey(lead.Email)){
                Contact contact = m3.get(lead.Email);
                Utils.mergeLeadWithContact(lead, contact);
                break;
            }
            
            if(lead.MobilePhone != null && lead.MobilePhone != 'null'){
                String phoneWithoutCountryCode = lead.MobilePhone.substring(lead.MobilePhone.length() - 9, lead.MobilePhone.length());
          
                if(m4.containsKey(phoneWithoutCountryCode)){
                    Contact contact = m4.get(phoneWithoutCountryCode);
                    Utils.mergeLeadWithContact(lead, contact); 
                    break;
                }
            }   
        }
    }
    
    System.debug(Limits.getCPUTime()); 
}

Utils being the apex class used for matching and conversion.

public class Utils {
  public static boolean hasTheSameAccountId(Lead lead, Account account) {
        return acccountIdIsNotNull(lead, account) && account.Account_ID__c == lead.Account_ID__c;
    }
    
    public static boolean hasTheSameSiret(Lead lead, Account account) {
        return siretIsNotNull(lead, account) && account.Siret__c == lead.Siret__c;
    }
    
    public static boolean acccountIdIsNotNull(Lead lead, Account account) {
        return account.Account_ID__c != null && lead.Account_ID__c != null;
    }
    
    public static boolean siretIsNotNull(Lead lead, Account account) {
        return account.Siret__c != null && lead.Siret__c != null;
    }
    
    public static boolean hasTheSameEmail(Lead lead, Contact contact) {
        return emailIsNotNull(lead, contact) && lead.Email == contact.Email;
    }
    
    public static boolean hasTheSameMobile(Lead lead, Contact contact) {
        return mobileIsNotNull(lead, contact) && Utils.mobilePhoneWithoutCodeCountry(lead.MobilePhone) == Utils.mobilePhoneWithoutCodeCountry(contact.MobilePhone);
    }
    
    public static String mobilePhoneWithoutCodeCountry(String phoneNumber) {
        return phoneNumber.length() <= 9 ? phoneNumber : phoneNumber.substring(phoneNumber.length() - 9, phoneNumber.length());
    }
    
    public static boolean emailIsNotNull(Lead lead, Contact contact) {
        return lead.Email != null && contact.Email != null;
    }
    
    public static boolean mobileIsNotNull(Lead lead, Contact contact) {
        return lead.MobilePhone != null && lead.MobilePhone != 'null' && contact.MobilePhone != null && contact.MobilePhone != 'null';
    }
    
    public static void mergeLeadWithAccount(Lead lead, Account account) {
      Database.LeadConvert lc = new Database.LeadConvert();
        lc.setLeadId(lead.Id);
        
        lc.setConvertedStatus('Qualifié');
        lc.setDoNotCreateOpportunity(true);
        lc.setAccountId(account.Id);
        lc.setOwnerId(account.OwnerId);
    
        try {
            Database.LeadConvertResult lcr = Database.convertLead(lc);
          System.assert(lcr.isSuccess());
        } catch (DmlException e) {
            System.debug('The following exception has occurred: ' + e.getMessage());
        }
    }
    
    public static void mergeLeadWithContact(Lead lead, Contact contact) {
      Database.LeadConvert lc = new Database.LeadConvert();
        lc.setLeadId(lead.Id);
        
        if(contact.AccountId != null) {
          lc.setConvertedStatus('Qualifié');
            lc.setDoNotCreateOpportunity(true);
            lc.setAccountId(contact.AccountId);
            lc.setOwnerId(contact.OwnerId);
            lc.setContactId(contact.Id);
            
            try {
                Database.LeadConvertResult lcr = Database.convertLead(lc);
        System.assert(lcr.isSuccess());
            } catch (DmlException e) {
                System.debug('The following exception has occurred: ' + e.getMessage());
            }
        }
    }
}

Answer 1

As mentioned already by @PhilW, you should probably consider to run this asynchronously, but if you have a good reason to choose apex trigger over async code, here are few comments. Not sure if you has already considered these, but you could be a better judge as you know the business requirement.

Firstly, in the following SOQL, you are querying all the accounts but later in the code looks like you are interested in only the accounts that are related to the leads being processed by the trigger (thru Account_ID__c or Siret__c). If the number of accounts created within the last 1 days is not huge, then it wouldn't matter. But if the other records (contacts) count is high or there are many accounts created within the past day (none of them related to the lead records being processed), then NOT fetching unnecessary records here would make some difference in the CPU usage time (specifically, because you are also creating a map out of this query result and looping thru it). Consider all the CPU time used on looping thru unnecessary records. Query only for the accounts related (thru Account_ID__c or Siret__c) to the leads being processed. [Besides, its best to avoid using null condition check to improve SOQL performance. Not that it would have any effect on CPU usage time, but a good practice]

List<Account> accounts = [SELECT ID, Account_ID__c, Siret__c, OwnerId FROM Account WHERE Account_ID__c != NULL AND CreatedDate = LAST_N_DAYS:1];

Secondly, similar comment as above for the following SOQL query. You are querying for all contacts created in last 30 days, but later in the code looks like you are interested in only contacts that have value for Email and MobilePhone fields. Incorporating these conditions in the SOQL query could have some performance improvement over the contact map collection and looping through it. [No comments on the substring extraction over the mobile field value. You could probably create formula fields in lead & contact objects and use them here, but I guess that would rather lead to higher CPU usage time. I've not tested that anyways.]

List<Contact> contacts = [SELECT ID, Email, MobilePhone, AccountId, OwnerId FROM Contact WHERE CreatedDate = LAST_N_DAYS:30];

With the above 2 comments, if you are able to get rid of some records in the SOQL results, it would mean smaller map collections and lesser time spent processing them.

Lastly, I'm not sure if this could reduce the CPU usage, but I guess its worth a try. In the for loop, you are calling mergeLeadWithAccount or mergeLeadWithContact static methods for every record. Have you considered bulkifying this piece of code? You probably know that Database.convertLead method also accepts array of Database.LeadConvert object (Database.LeadConvert[]) [Note: This is not going to reduce the number of DML executions and that is anyways not related to the CPU usage time, but I would like to find out if bulkifying would make any difference at all in the CPU usage time esp. in case of high record volume processing]

for(Lead lead : trigger.new) {
....
....
)

Answer 2

Thank you all for your answers.

Just to provide feedback, I succeeded, thanks to your advises to rewrite my code and be more specific directly in queries.

trigger LeadDeduplicate2 on Lead (after insert) {
    for (Lead lead : trigger.new) {
        if (lead.IsExternal__c) {
            List<Account> accounts;
            if (lead.Account_ID__c != null && lead.Siret__c != null) {
                accounts = [SELECT ID, Account_ID__c, Siret__c, OwnerId FROM Account WHERE (Account_ID__c = :lead.Account_ID__c OR Siret__c = :lead.Siret__c) AND CreatedDate = LAST_N_DAYS:60];
            } else if (lead.Account_ID__c != null) {
                accounts = [SELECT ID, Account_ID__c, Siret__c, OwnerId FROM Account WHERE Account_ID__c = :lead.Account_ID__c AND CreatedDate = LAST_N_DAYS:60];
            } else if (lead.Siret__c != null) {
                accounts = [SELECT ID, Account_ID__c, Siret__c, OwnerId FROM Account WHERE Siret__c = :lead.Siret__c AND CreatedDate = LAST_N_DAYS:60];
            }

            if (accounts != null) {
                Boolean accountMerge = false;
                for (Account myAccount :accounts) {
                  Utils.mergeLeadWithAccount(lead, myAccount);
                  accountMerge = true;
                }

                if (accountMerge) {
                  break;
                }
            }

            List<Contact> contacts;
            if(lead.mobile_nine_digit__c != null && lead.Email != null) {
                contacts = [SELECT ID, Email, mobile_nine_digit__c, AccountId, OwnerId FROM Contact WHERE (Email = :lead.Email OR mobile_nine_digit__c = :lead.mobile_nine_digit__c) AND CreatedDate = LAST_N_DAYS:60];
            } else if (lead.Email != null) {
                contacts = [SELECT ID, Email, mobile_nine_digit__c, AccountId, OwnerId FROM Contact WHERE Email = :lead.Email AND CreatedDate = LAST_N_DAYS:60];
            } else if (lead.mobile_nine_digit__c != null) {
                contacts = [SELECT ID, Email, mobile_nine_digit__c, AccountId, OwnerId FROM Contact WHERE mobile_nine_digit__c = :lead.mobile_nine_digit__c AND CreatedDate = LAST_N_DAYS:60];
            }

            if (contacts != null) {
                for(Contact myContact :contacts){
                    Utils.mergeLeadWithContact(lead, myContact);
                }
            }
        }
    }
}

I don't know if it's optimized but at least it seems to work. I've not tried asynchronous trigger yet but I'll have a look to it in order to have a persistent code.

However, i'm now facing errors with process builders that are triggered when an account, a lead or a contact is created. It says : "UNABLE_TO_LOCK_ROW". So I was wondering if it comes from my new code and if it can be avoided. I read the documentation salesforce is providing on this case but I'm afraid I'll get dmlException if I'm using for update in my code.

Do you have any thoughts on this point ?

Answer 3

Two things that I would like to touch up on based on your updated code

1) You should avoid writing queries in for loop. You can further trim your code by adding account ids and siretids to set and do an OR on the query so that you can avoid chain of if statements

Your updated code looks like this

trigger LeadDeduplicate2 on Lead (after insert) {
set<id> accountid = new set<id>();
set<id> siretid = new set<id>();
    for (Lead lead : trigger.new) {
        if (lead.IsExternal__c) {
            accountid.add(lead.Account_ID__c);
            siretid.add(lead.Siret__c);
            }
}
//Querying outside loop

//This will retrieve accounts for which account id or siret id is not null

List<Account> accounts = [SELECT ID, Account_ID__c, Siret__c, OwnerId 
FROM Account where CreatedDate = LAST_N_DAYS:60 WHERE (Account_ID__c = :accountid OR Siret__c = :siretid)] 

            if (accounts != null) {
                Boolean accountMerge = false;
                for (Account myAccount :accounts) {
                  Utils.mergeLeadWithAccount(lead, myAccount);
                  accountMerge = true;
                }

                if (accountMerge) {
                  break;
                }
            }
** You can follow the same format for contacts and trim the code **
            List<Contact> contacts;
            if(lead.mobile_nine_digit__c != null && lead.Email != null) {
                contacts = [SELECT ID, Email, mobile_nine_digit__c, AccountId, OwnerId FROM Contact WHERE (Email = :lead.Email OR mobile_nine_digit__c = :lead.mobile_nine_digit__c) AND CreatedDate = LAST_N_DAYS:60];
            } else if (lead.Email != null) {
                contacts = [SELECT ID, Email, mobile_nine_digit__c, AccountId, OwnerId FROM Contact WHERE Email = :lead.Email AND CreatedDate = LAST_N_DAYS:60];
            } else if (lead.mobile_nine_digit__c != null) {
                contacts = [SELECT ID, Email, mobile_nine_digit__c, AccountId, OwnerId FROM Contact WHERE mobile_nine_digit__c = :lead.mobile_nine_digit__c AND CreatedDate = LAST_N_DAYS:60];
            }

            if (contacts != null) {
                for(Contact myContact :contacts){
                    Utils.mergeLeadWithContact(lead, myContact);
                }
            }
        }
    }
}

2) Unable to lockrow can happen when multiple users are trying to the same record or if there is any other automation is trying to update the same record which we cant really say without the code snippet you have posted. What you can do is when querying the accounts and contacts in the operation use FOR UPDATE keyword in the query so that the records will get temporarily locked for you to perform the updates and the stops the other automation from updating the record at the same time.

Your query would look like

List accounts = [SELECT ID, Account_ID__c, Siret__c, OwnerId FROM Account where CreatedDate = LAST_N_DAYS:60 WHERE (Account_ID__c = :accountid OR Siret__c = :siretid) FOR UPDATE].

Let me know if this helps!