You need to sign in to do that
Don't have an account?
Rajbharath
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:
My Trigger:
Debug log:
Please help with me some pointers in resolving the issue
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
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
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.
Can you email me at naresh48.mitts@gmail.com, I want to discuss with you about the salesforce.