+ Start a Discussion
RajbharathRajbharath 

Need help in solving System.LimitException: Too many query rows: 50001 error

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​​
Best Answer chosen by Rajbharath
Naveen IlaNaveen Ila
In Salesforce, Per Transaction we fetch at Max 50000 records. It is not per SOQL. 

Suppose, in transaction we have 2 SOQL quries
First SOQL  fetching 10 records 
Second SOQL fetching 20 records 
Then the the records we fetched 30 (which will counted against to the Governer limits) 

In your case, you mentioned 50000 in first SOQL(line no 19-29) and 50000 in second one(line no 142) as well. 

 

All Answers

Naveen IlaNaveen Ila
In Salesforce, Per Transaction we fetch at Max 50000 records. It is not per SOQL. 

Suppose, in transaction we have 2 SOQL quries
First SOQL  fetching 10 records 
Second SOQL fetching 20 records 
Then the the records we fetched 30 (which will counted against to the Governer limits) 

In your case, you mentioned 50000 in first SOQL(line no 19-29) and 50000 in second one(line no 142) as well. 

 
This was selected as the best answer
krish48krish48
Hello Naveen,
Can you email me at naresh48.mitts@gmail.com, I want to discuss with you about the salesforce.