Need help with System.LimitException: Apex CPU time limit exceeded

Question

I am receiving the following error, when trying to bulk load the customer records. Some of them are uploaded successfully, while some of them are throwing the following error.

<message>
AccountTrigger: System.LimitException: Apex CPU time limit exceeded
</message>
<statusCode>CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY</statusCode>

Please find the status shown in debug logs below:

  Number of SOQL queries: 1 out of 100
  Number of query rows: 50000 out of 50000 ******* CLOSE TO LIMIT
  Number of SOSL queries: 0 out of 20
  Number of DML statements: 0 out of 150
  Number of DML rows: 0 out of 10000
  Maximum CPU time: 15026 out of 10000 ******* CLOSE TO LIMIT
  Maximum heap size: 0 out of 6000000
  Number of callouts: 0 out of 100
  Number of Email Invocations: 0 out of 10
  Number of future calls: 0 out of 50
  Number of queueable jobs added to the queue: 0 out of 50
  Number of Mobile Apex push calls: 0 out of 10

There are around 800,000 existing records in the database. I think when the query in trigger is trying to match the existing records, it is throwing exception.

My question is in situations like this, we should go with Batch Apex right?

If there is any other work around, Please suggest.

Please find my trigger logic below:

public class AccountInsertTriggerHandler {
private List<Account> trgOldList = new List<Account> ();
private List<Account> trgNewList = new List<Account> ();
private Map<id,Account> trgOldMap = new Map<id,Account>();
private Map<id,Account> trgNewMap = new Map<id,Account>();
public Map<String,Account> leadsMap = new Map<String,Account>();
private List<String> LeadList = new List<String> ();

public AccountInsertTriggerHandler(List<Account> trgOldList,List<Account> trgNewList,Map<id,Account> trgOldMap,Map<id,Account> trgNewMap){
    this.trgOldList = trgOldList;
    this.trgNewList = trgNewList;
    this.trgOldMap = trgOldMap;
    this.trgNewMap = trgNewMap;

    for(Account acct: trgNewList){
        String UniqueCustID = acct.Region__pc + '-' + acct.PersonEmail;
        LeadList.add(UniqueCustID);
    }
    List<Account> allLeads = [select AP_21_Per_IDX__c, AP21_perIDx__pc, AP21_Code__pc, Salutation, AP21_Initials__pc, 
                                FirstName, LastName, Gender__pc, PersonBirthdate, AP21_StartDate__pc, PersonTitle, AP21_Password__pc,
                                 AP21_UpdateTimestamp__pc, AP21_Privacy__pc, AP21_Reference_Sales_rep__pc, BillingStreet, 
                                 AP21_Billing_AddressLine2__pc, BillingCity, BillingState, BillingPostalCode, BillingCountry, 
                                 ShippingStreet, ShippingCity, ShippingState, ShippingPostalCode, 
                                 ShippingCountry, PersonEmail, Home_Phone__pc, PersonMobilePhone, Business_Phone__pc, 
                                 AP21_Loyalty_ID__pc, AP21_LoyaltyTypeID__pc, AP21_LoyaltyType__pc, AP21_Loyalty_CardNo__pc, 
                                 AP21_Loyalty_CardExpiry__pc, AP21_Loyalty_Balance__pc, AP21_Loyalty_CreditStatus__pc, 
                                 AP21_Loyalty_Message__pc, AP21_Loyalty_JoinDate__pc, AP21_LoyaltyStatusID__pc, 
                                 AP21_Loyalty_Currency__pc, AP21_Loyalty_Code__pc, AP21_Loyalty_Format__pc, Store__pc, Region__pc
                                 from Account where AP_21_Per_IDX__c = null and UniqueID__pc IN : LeadList];
    for (Account accLeads: allLeads){
        String UniqueLeadsID = accLeads.Region__pc + '-' + accLeads.PersonEmail;
        leadsMap.put(UniqueLeadsId,accLeads);
        System.debug(leadsMap);
    }
}
public void OnAfterInsert(){
    CompareCustomersToLeads(trgNewList,null);
}
/*public void OnBeforeInsert(){
    CompareCustomersToLeads(trgNewList,null);
}*/
public void CompareCustomersToLeads(List<Account> trgNewList,Map<id,Account> trgOldMap){
    //Add to map using Region and emailAddress as Key and Account record as value.Only add records from Flow User
    ////Retrieve all leads (where PerIdx is blank and created by not Flow) and create map
    ////compare similiar records in the maps i.e for each customer record in customer map, find lead record
    //compare values and update lead with PerIdx and other values
    Map<String,Account> customerMap = new Map<String,Account>();
   // Map<String,Account> leadsMap = new Map<String,Account>();
    List<Account> leadsToUpdate = new List<Account>();
    List<Account> customersToDelete = new List<Account>();
    for(Account acc: trgNewList){
        String UniqueID = acc.Region__pc + '-' + acc.PersonEmail;
        if(leadsMap.containsKey(UniqueID)){
            customerMap.put(UniqueID,acc);
            //acc.addError('No Match to Lead hence cannot Insert');
        }

        System.debug(customerMap);

    }
    for(String key:customerMap.keySet()){
        System.debug('********KEY ' + key);
        if(leadsMap.containsKey(key)){
            Account customerRecord = customerMap.get(key);
            Account leadsRecord = leadsMap.get(key);
            System.debug(customerRecord);
            System.debug(leadsRecord);

            if(customerRecord.AP_21_Per_IDX__c!=null &&leadsRecord.AP_21_Per_IDX__c==null){
                System.debug('*********** Met Conditions');
                leadsRecord.AP_21_Per_IDX__c = customerRecord.AP_21_Per_IDX__c;
                leadsRecord.AP21_perIDx__pc = customerRecord.AP21_perIDx__pc;
                leadsRecord.FirstName = customerRecord.FirstName;
                leadsRecord.LastName = customerRecord.LastName;
                leadsRecord.PersonMobilePhone = customerRecord.PersonMobilePhone;
                if(leadsRecord.Gender__pc==null)
                    leadsRecord.Gender__pc = customerRecord.Gender__pc;
                if(leadsRecord.PersonBirthdate==null)
                    leadsRecord.PersonBirthdate = customerRecord.PersonBirthdate;
                if(leadsRecord.Store__pc==null)
                    leadsRecord.Store__pc = customerRecord.Store__pc;
                leadsRecord.AP21_Code__pc = customerRecord.AP21_Code__pc;
                leadsRecord.Salutation = customerRecord.Salutation;
                leadsRecord.AP21_Initials__pc = customerRecord.AP21_Initials__pc;
                leadsRecord.AP21_StartDate__pc = customerRecord.AP21_StartDate__pc;

                leadsRecord.PersonTitle = customerRecord.PersonTitle;
                leadsRecord.AP21_Password__pc = customerRecord.AP21_Password__pc;
                leadsRecord.AP21_UpdateTimestamp__pc = customerRecord.AP21_UpdateTimestamp__pc;
                leadsRecord.AP21_Privacy__pc = customerRecord.AP21_Privacy__pc;
                leadsRecord.AP21_Reference_Sales_rep__pc = customerRecord.AP21_Reference_Sales_rep__pc;
                leadsRecord.BillingStreet = customerRecord.BillingStreet;
                leadsRecord.AP21_Billing_AddressLine2__pc = customerRecord.AP21_Billing_AddressLine2__pc;

                leadsRecord.BillingCity = customerRecord.BillingCity;
                leadsRecord.BillingState = customerRecord.BillingState;
                leadsRecord.BillingPostalCode = customerRecord.BillingPostalCode;
                leadsRecord.BillingCountry = customerRecord.BillingCountry;

                leadsRecord.ShippingStreet = customerRecord.ShippingStreet;
                leadsRecord.ShippingCity = customerRecord.ShippingCity;
                leadsRecord.ShippingState = customerRecord.ShippingState;
                leadsRecord.ShippingPostalCode = customerRecord.ShippingPostalCode;
                leadsRecord.ShippingCountry = customerRecord.ShippingCountry;
                leadsRecord.PersonEmail = customerRecord.PersonEmail;
                if(leadsRecord.Home_Phone__pc==null)
                 leadsRecord.Home_Phone__pc = customerRecord.Home_Phone__pc;
                if(leadsRecord.PersonMobilePhone==null)
                 leadsRecord.PersonMobilePhone = customerRecord.PersonMobilePhone;
                if(leadsRecord.Business_Phone__pc==null)
                 leadsRecord.Business_Phone__pc = customerRecord.Business_Phone__pc;



                leadsRecord.AP21_Loyalty_ID__pc = customerRecord.AP21_Loyalty_ID__pc;
                leadsRecord.AP21_LoyaltyTypeID__pc = customerRecord.AP21_LoyaltyTypeID__pc;
                leadsRecord.AP21_LoyaltyType__pc = customerRecord.AP21_LoyaltyType__pc;
                leadsRecord.AP21_Loyalty_CardNo__pc = customerRecord.AP21_Loyalty_CardNo__pc;
                leadsRecord.AP21_Loyalty_CardExpiry__pc = customerRecord.AP21_Loyalty_CardExpiry__pc;
                leadsRecord.AP21_Loyalty_Balance__pc = customerRecord.AP21_Loyalty_Balance__pc;
                leadsRecord.AP21_Loyalty_CreditStatus__pc = customerRecord.AP21_Loyalty_CreditStatus__pc;

                leadsRecord.AP21_Loyalty_Message__pc = customerRecord.AP21_Loyalty_Message__pc;
                leadsRecord.AP21_Loyalty_JoinDate__pc = customerRecord.AP21_Loyalty_JoinDate__pc;
                leadsRecord.AP21_LoyaltyStatusID__pc = customerRecord.AP21_LoyaltyStatusID__pc;
                leadsRecord.AP21_Loyalty_Currency__pc = customerRecord.AP21_Loyalty_Currency__pc;
                leadsRecord.AP21_Loyalty_Code__pc = customerRecord.AP21_Loyalty_Code__pc;
                leadsRecord.AP21_Loyalty_Format__pc = customerRecord.AP21_Loyalty_Format__pc;
                leadsRecord.Region__pc = customerRecord.Region__pc;




                leadsToUpdate.add(leadsRecord);

                customersToDelete.add(customerRecord);
            }

        }
    }
    update leadsToUpdate;
    List<Account> deleteCustomers = [select id from Account where id IN: customersToDelete];
    delete deleteCustomers;

}

}

Please find the debug log after removing the debug statements in production code:

10:09:58.714 (18562455772)|SOQL_EXECUTE_BEGIN|[152]|Aggregations:0|SELECT id FROM Account WHERE id IN :tmpVar1 LIMIT 50000

10:09:58.714 (18569696442)|SOQL_EXECUTE_END|[152]|Rows:1 10:09:58.714 (18569734502)|EXCEPTION_THROWN|[152]|System.LimitException: Too many query rows: 50001 10:09:58.714 (18569853312)|HEAP_ALLOCATE|[152]|Bytes:30 10:09:58.714 (18569880194)|METHOD_EXIT|[47]|01p7F00000O4xqM|AccountInsertTriggerHandler.CompareCustomersToLeads(List, Map) 10:09:58.714 (18569890597)|METHOD_EXIT|[4]|01p7F00000O4xqM|AccountInsertTriggerHandler.OnAfterInsert() 10:09:58.714 (18569947597)|FATAL_ERROR|System.LimitException: Too many query rows: 50001

Class.AccountInsertTriggerHandler.CompareCustomersToLeads: line 152, column 1 Class.AccountInsertTriggerHandler.OnAfterInsert: line 47, column 1 Trigger.AccountTrigger: line 4, column 1 10:09:58.714 (18569960836)|FATAL_ERROR|System.LimitException: Too many query rows: 50001

Class.AccountInsertTriggerHandler.CompareCustomersToLeads: line 152, column 1 Class.AccountInsertTriggerHandler.OnAfterInsert: line 47, column 1 Trigger.AccountTrigger: line 4, column 1 10:09:59.570 (18570083066)|CUMULATIVE_LIMIT_USAGE 10:09:59.570 (18570083066)|LIMIT_USAGE_FOR_NS|(default)| Number of SOQL queries: 2 out of 100 Number of query rows: 50001 out of 50000 ******* CLOSE TO LIMIT Number of SOSL queries: 0 out of 20 Number of DML statements: 1 out of 150 Number of DML rows: 3 out of 10000 Maximum CPU time: 5296 out of 10000 ******* CLOSE TO LIMIT Maximum heap size: 0 out of 6000000 Number of callouts: 0 out of 100 Number of Email Invocations: 0 out of 10 Number of future calls: 0 out of 50 Number of queueable jobs added to the queue: 0 out of 50 Number of Mobile Apex push calls: 0 out of 10

10:09:59.570 (18570083066)|CUMULATIVE_LIMIT_USAGE_END

10:09:58.714 (18571196926)|CODE_UNIT_FINISHED|AccountTrigger on Account trigger event AfterInsert|__sfdc_trigger/AccountTrigger 10:09:58.714 (18572303315)|EXECUTION_FINISHED

Answer 1

Your debug statements are sucking the performance right out of your code. Never leave debug statements in production code. And I do mean never. Let me give you a trivial example.

Map<Integer, Integer> values = new Map<Integer, Integer>();
for(Integer i = 0; i < 7500; i++) {
    values.put(i, i);
    system.debug('item'+values);
}

This code tries to put 7,500 items into a map. It requires about 9.7 seconds to run:

  Number of SOQL queries: 0 out of 100
  Number of query rows: 0 out of 50000
  Number of SOSL queries: 0 out of 20
  Number of DML statements: 0 out of 150
  Number of DML rows: 0 out of 10000
  Maximum CPU time: 9714 out of 10000 ******* CLOSE TO LIMIT
  Maximum heap size: 0 out of 6000000
  Number of callouts: 0 out of 100
  Number of Email Invocations: 0 out of 10
  Number of future calls: 0 out of 50
  Number of queueable jobs added to the queue: 0 out of 50
  Number of Mobile Apex push calls: 0 out of 10

Now, we remove the debug statement:

  Number of SOQL queries: 0 out of 100
  Number of query rows: 0 out of 50000
  Number of SOSL queries: 0 out of 20
  Number of DML statements: 0 out of 150
  Number of DML rows: 0 out of 10000
  Maximum CPU time: 0 out of 10000
  Maximum heap size: 0 out of 6000000
  Number of callouts: 0 out of 100
  Number of Email Invocations: 0 out of 10
  Number of future calls: 0 out of 50
  Number of queueable jobs added to the queue: 0 out of 50
  Number of Mobile Apex push calls: 0 out of 10

The script took so little time it didn't even register in the governor limits. The exact same code minus debug statements. Now, I'm not saying that this will fix your issue, but considering that you're using debug statements in every single loop, I can pretty much guarantee it'll save at least 5 seconds of CPU time, if not more.

If you find yourself in a position where you need to debug, use checkpoints instead. They will give you a complete memory graph at any arbitrary point of execution, and when you're done, you can disable them so they don't take up tons of CPU time.