You need to sign in to do that
Don't have an account?
System.LimitException: Too many query rows: 50001
Can anyone help me with this error? I thought I had it setup the recommended way but I must not. Thanks!
What this code is doing is filling in lookup fields, in essences; it's allowing us to load data without having to use a SF Id for related objects. For example, we're using Data Loader to load insurance policies (30,000+ records). The file has external ids like an Agent Id and SSN so we use those ids to find their matching SF Id.
Here's my trigger. Line 23 is the location of the error
for (Contact c:[SELECT Id, Agent_Number__c FROM Contact where Agent_Number__c in:agId])
trigger policyBefore on Policy__c (before insert, before update) { // When a policy is created, link the record to the Contact's (Employee) record based on the provided SSN // When a policy is created, lookup the Plan Name from the Plan table using the Plan Code // When a policy is created, link the record to the Contact's (Agent) record based on the provided Agent Id // Create a set of all the SSN numbers (Employee ID) // Create a set of all the Agent numbers (Agent_Number__c) set<string> eeId = new set<string>(); set<string> agId = new set<string>(); for (Policy__c p1 :trigger.new) { if (p1.Employee_SSN__c <> '') eeId.add(p1.Employee_SSN__c); if (p1.Agent_Number__c <> '') agId.add(p1.Agent_Number__c); } // Create a map of the Employee ID (SSN) and SF Contact Id. This will be used to resolve the Policy__c.Employee__c field map<string, id> sfContId = new map<string, id>(); for (Contact c:[SELECT Id, SSN__c FROM Contact where SSN__c in:eeId]){ sfContId.put(c.SSN__c, c.Id); } // Create a map of the Agent Id (Agent_Number__c) and SF Contact Id. This will be used to resolve the Policy__c.Agent__c field map<string, id> sfAgentId = new map<string, id>(); for (Contact c:[SELECT Id, Agent_Number__c FROM Contact where Agent_Number__c in:agId]){ sfAgentId.put(c.Agent_Number__c, c.Id); } // Create a map of the Plan Number (Name) and Plan Name. This will be used to resolve the Policy__c.Plan_Name__c field map<string, string> planName = new map<string, string>(); for (Plan__c p2:[SELECT Name, Plan_Name__c FROM Plan__c]){ planName.put(p2.Name, p2.Plan_Name__c); } // Spin through each incoming record and set the Employee__c (Contact Id) field for each record for (Policy__c p :trigger.new){ if (p.Name == null) p.Name = p.Policy_Number_DataLoad__c; if (p.Employee_SSN__c <> null) { p.Employee__c = sfContId.get(p.Employee_SSN__c); } if (p.Agent_Number__c <> null) { p.Agent__c = sfAgentId.get(p.Agent_Number__c); } if (p.Plan_Code__c <> null) { p.Plan_Name__c = planName.get(p.Plan_Code__c); } } }
So as per your post I see that you have issue with the following query....and I believe SSN's on contact object are unique right?.. Do you really have multiple(Many) contacts with same Agent Number??
for (Contact c:[SELECT Id, Agent_Number__c FROM Contact where Agent_Number__c in:agId]){
sfAgentId.put(c.Agent_Number__c, c.Id);
}
If you have multiple contacts with same Agent Numbers...
eg: (ContactId ->AgentNumber)
contact1.Id ->1, contact2.Id->1, Contact3.Id->1
then the map sfAgentId would have only one agent number as key and some contactId as value (It can be any contact of the above 3)
so if that is really the use case.. All I could think of is a dirty way to achieve this..
Instead of above for loop use the below code..
contact c = new contact();
for(String agtId : agid ){
c = [select Agent_Number__c, Id from contact where Agent_Number__c = :agtId LIMIT 1];
sfAgentId.put(c.Agent_Number__c,c.Id);
}
When Loading the data make sure that your batch size is not more than 80 or 90 as you can have only 100 soql queries (max per batch)
For best practices on dealing with limits you should go through this.. this might help
http://wiki.developerforce.com/page/Apex_Code_Best_Practices
http://www.salesforce.com/us/developer/docs/apexcode/index_Left.htm#StartTopic=Content/apex_gov_limits.htm
All Answers
Code looks good. I believe only way is not to use BULK API when using data loader and in serial mode try decreasing the batch size from 200 to 150/100/50.
Thanks for the reply Sam. I'll try the batch size limit as you suggested. One odd piece to this is that even my simple test class fails with the same error. My test class only inserts a couple records. It's almost like the WHERE clause isn't filtering out the data. ??
Can you post your test class? What API Version are you using?
Here's my test class. API Version 20.0
Update it to Version 24.0 or greater and see if it works.
Okay, I feel stupid now... but I can't seem to figure out how to update to the latest version. Help?
Edit the class and then click on version settings tab (Next to Apex Class Tab) select the version and then click save.
Hi Sam,
First, thank you for your continued help! I was able to update my force ide to 25.0. That did solve my test class issues. I just heard form my customer and it did not resolve the loading of about 19,000 policies. Any other ideas?
So as per your post I see that you have issue with the following query....and I believe SSN's on contact object are unique right?.. Do you really have multiple(Many) contacts with same Agent Number??
for (Contact c:[SELECT Id, Agent_Number__c FROM Contact where Agent_Number__c in:agId]){
sfAgentId.put(c.Agent_Number__c, c.Id);
}
If you have multiple contacts with same Agent Numbers...
eg: (ContactId ->AgentNumber)
contact1.Id ->1, contact2.Id->1, Contact3.Id->1
then the map sfAgentId would have only one agent number as key and some contactId as value (It can be any contact of the above 3)
so if that is really the use case.. All I could think of is a dirty way to achieve this..
Instead of above for loop use the below code..
contact c = new contact();
for(String agtId : agid ){
c = [select Agent_Number__c, Id from contact where Agent_Number__c = :agtId LIMIT 1];
sfAgentId.put(c.Agent_Number__c,c.Id);
}
When Loading the data make sure that your batch size is not more than 80 or 90 as you can have only 100 soql queries (max per batch)
For best practices on dealing with limits you should go through this.. this might help
http://wiki.developerforce.com/page/Apex_Code_Best_Practices
http://www.salesforce.com/us/developer/docs/apexcode/index_Left.htm#StartTopic=Content/apex_gov_limits.htm
We're good now. You're questioning of the Agent Id made them think that possibly that query wasn't working as expected and it wasn't.
We're using Contacts for both Policy holders and Agents (distingished by record types) so the Contact query can be quite large if not filtered correctly. What seems to have been happening in my query was I was getting any record that didn't have an Agent Id (all policy owners). I added to the Where clause to filter out empty Agent Ids and it seems to be working well again.
Thank you so much for your help! Greatly appreciated,
Terry