function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Terry411Terry411 

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);
		}
	}	
}

 

Best Answer chosen by Admin (Salesforce Developers) 
Saikishore Reddy AengareddySaikishore Reddy Aengareddy

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

Saikishore Reddy AengareddySaikishore Reddy Aengareddy

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. 

Terry411Terry411

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.  ??

Saikishore Reddy AengareddySaikishore Reddy Aengareddy

Can you post your test class? What API Version are you using?

Terry411Terry411

Here's my test class.  API Version 20.0

 

@isTest
public class TestMHIns {
	
    static testMethod void myUnitTest() {
		Profile pf = [SELECT Id FROM profile WHERE name='System Administrator'];

    	User u1 = new User (LastName='User1', alias='User1', email='user1@user.com', username='WFuser1@user.com', profileid = pf.Id, 
    				emailencodingkey='UTF-8', languagelocalekey='en_US', timezonesidkey='America/Chicago', localesidkey='en_US');
		insert u1;

		Id rtAcctCarrier = [Select Id From RecordType where sObjectType='Account' and Name='Carrier' and isActive=true].Id;
		Id rtAcctCompany = [Select Id From RecordType where sObjectType='Account' and Name='Company' and isActive=true].Id;
		Id rtAcctAgency = [Select Id From RecordType where sObjectType='Account' and Name='Agency' and isActive=true].Id;
		Id rtContCarrier = [Select Id From RecordType where sObjectType='Contact' and Name='Carrier Contact' and isActive=true].Id;
		Id rtContEmployee = [Select Id From RecordType where sObjectType='Contact' and Name='Employee Contact' and isActive=true].Id;
		Id rtContAgent = [Select Id From RecordType where sObjectType='Contact' and Name='Agent Contact' and isActive=true].Id;

        // Create test records
        Account a1 = new Account (Name='Carrier', RecordTypeId=rtAcctCarrier);
		Account a2 = new Account (Name='Company', RecordTypeId=rtAcctCompany);
		Account a3 = new Account (Name='Agency', RecordTypeId=rtAcctAgency);
		Account[] a = new Account[] {a1, a2, a3};
		insert a;
		
        Plan__c pl1 = new Plan__c (Name='code1', Plan_Name__c='codeName1');
        Plan__c pl2 = new Plan__c (Name='code2', Plan_Name__c='codeName2');
        Plan__c[] pl = new Plan__c[] {pl1, pl2};
        insert pl;
        
        Locations__c l1 = new Locations__c (Name='location1', Location_Name__c='locationName1', District__c='district1', Account__c=a1.id, Agent_s_User_Id__c=u1.Id);
        Locations__c l2 = new Locations__c (Name='location2', Location_Name__c='locationName2', District__c='district2', Account__c=a2.id, Agent_s_User_Id__c=u1.Id);
        Locations__c[] l = new Locations__c[] {l1, l2};
        insert l;
        
		Contact c1 = new Contact (AccountId = a1.Id, lastName='Contact', email='contact@contact.com', RecordTypeId=rtContCarrier);
		Contact c2 = new Contact (AccountId = a2.Id, lastName='Employee', email='employee@employee.com', SSN__c='1Contact1', Location_Number__c='location2', RecordTypeId=rtContEmployee);
		Contact c3 = new Contact (AccountId = a3.Id, lastName='Agent', email='agent@agent.com', Agent_Number__c='1Agent1', RecordTypeId=rtContAgent);
		Contact c4 = new Contact (AccountId = a2.Id, lastName='Employee2', SSN__c='2Contact2', Location_Number__c='location2');
		Contact[] c = new Contact[] {c1, c2, c3, c4};
		insert c;   
        
		Policy__c p1 = new Policy__c (Name='Policy1', Carrier__c = a1.Id, Agent_Number__c = '1Agent1', Employee_SSN__c='1Contact1', Plan_Code__c='code1');
		Policy__c p2 = new Policy__c (Name='Policy2', Carrier__c = a1.Id, Agent__c = c3.Id, Employee_SSN__c='1Contact1', Plan_Code__c='code2');
		Policy__c[] p = new Policy__c[] {p1, p2};
		insert p;
		
		Deductions__c d1 = new Deductions__c (Name='deduction1', Employee_SSN__c='1Contact1');
		Deductions__c d2 = new Deductions__c (Name='deduction2', Employee_SSN__c='1Contact1');
		Deductions__c[] d = new Deductions__c[] {d1, d2};
		insert d;
		
		
        // Test the results
		string iTest1 = (string)[Select Employee__c From Policy__c where Id=: p1.Id].Employee__c;
		system.debug('====================  iTest1 = ' + iTest1);
		system.assertEquals(c2.Id, iTest1);
        
		string iTest2 = (string)[Select Plan_Name__c From Policy__c where Id=: p1.Id].Plan_Name__c;
		system.debug('====================  iTest2 = ' + iTest2);
		system.assertEquals('codeName1', iTest2);
        
		string iTest3 = (string)[Select Location_Name__c From Contact where Id=: c2.Id].Location_Name__c;
		system.debug('====================  iTest3 = ' + iTest3);
		system.assertEquals('locationName2', iTest3);
        
		string iTest4 = (string)[Select Employee__c From Deductions__c where Id=: d1.Id].Employee__c;
		system.debug('====================  iTest4 = ' + iTest4);
		system.assertEquals(c2.Id, iTest4);
        
		string iTest5 = (string)[Select Agent__c From Policy__c where Id=: p1.Id].Agent__c;
		system.debug('====================  iTest5 = ' + iTest5);
		system.assertEquals(c3.Id, iTest5);
    }
}

 

Saikishore Reddy AengareddySaikishore Reddy Aengareddy

Update it to Version 24.0 or greater and see if it works.

Terry411Terry411

Okay, I feel stupid now... but I can't seem to figure out how to update to the latest version.  Help?

Saikishore Reddy AengareddySaikishore Reddy Aengareddy

Edit the class and then click on version settings tab (Next to Apex Class Tab) select the version and then click save.

Terry411Terry411

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?

Saikishore Reddy AengareddySaikishore Reddy Aengareddy

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

 

This was selected as the best answer
Terry411Terry411

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