Hello all,

I wrote a trigger which performs the following operation. Whenever data is uploaded into Customer object, it checks for existing records, and if found any, updates the fields with new record. In Production, I need to upload around 400,00 records. Now when I am trying to load the data, It's throwing me the following error "System.LimitException: Too many query rows: 50001".

My trigger handler class:

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 limit 50000];
        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 limit 50000];
        delete deleteCustomers;
        
    }

My Trigger:
 

trigger AccountTrigger on Account (after insert) {
    AccountInsertTriggerHandler accInsTriggerHandler = new AccountInsertTriggerHandler(Trigger.old,Trigger.new,Trigger.oldmap,Trigger.newmap);
    if(Trigger.isInsert)
        accInsTriggerHandler.OnAfterInsert();
    //if(Trigger.isInsert)
        //accInsTriggerHandler.OnBeforeInsert();

}

Debug log:
 

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<Account>, Map<Id,Account>)
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

Please help with me some pointers in resolving the issue​​