Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Prasanth Reddy MPrasanth Reddy M 

system.limitexception: too many query rows: 50001 in Production Instance

Hi,

For below trigger, when i have deployed in production, i am getting the "limit exception : too many query rows : 50001" 
trigger TaskUpdatewithLeadComments on Task (after insert) {
     Set<Id> leadids = new Set<Id>();
    List<Task> taskstoupdate = new list<Task>();
    
    //Fetch only Leads which follow criteria
    for(Lead ld : [ SELECT Id, company  FROM lead ])
        {
            leadids.add(ld.Id);
    }
    //Iterate over all Leads and modify Tasks for them + Add to a list variable 
    for(Lead ld : [SELECT Id, Prospect_notes__c,(SELECT Id, Whatid, Description FROM Tasks) from Lead Where Id IN : leadids])
    
     {
        for(Task t : ld.Tasks) {
        
           if(t.Description==null)
            t.Description = ld.prospect_notes__c;
            taskstoupdate.add(t);
        }
    }
    
    //Update Tasks
    if(taskstoupdate.size()>0) {
        update taskstoupdate;
    }
}

how can i fix this ?
Best Answer chosen by Prasanth Reddy M
Amit Chaudhary 8Amit Chaudhary 8
Where condtion is good and you code will work in production but once Data in Lead Trigger will increase 50K + where Pardot_notes__c='Yes' then same issue come again. That will better if you can change your trigger like below code 
trigger TaskUpdatewithLeadComments on Task (before insert) 
{
	String keyPrefix = Lead.sObjectType.getDescribe().getKeyPrefix();
	Set<ID> setLeadId = new Set<ID>();

	for(Task tsk : Trigger.new)
	{
		if(  tsk.whoId != null && (tsk.whoId).startsWith(keyPrefix)  )
		{
			setLeadId.add(tsk.whoId);
		}
	}
	
	Map<Id,Lead> mapLead = new Map<Id,Lead>([SELECT Id, Prospect_notes__c from Lead Where Id IN : setLeadId ]);
	for(Task tsk : Trigger.new)
	{
		if( tsk.whoId != null &&  (tsk.whoId).startsWith(keyPrefix) && mapLead.containsKey(tsk.whoId) )
		{
		 	Lead led = mapLead.get(tsk.whoId);
            tsk.Description = led.prospect_notes__c;
		}
	}
}

 

All Answers

Amit Chaudhary 8Amit Chaudhary 8
Please try below code and add limit . 
trigger TaskUpdatewithLeadComments on Task (after insert) {
     Set<Id> leadids = new Set<Id>();
    List<Task> taskstoupdate = new list<Task>();
    
    //Fetch only Leads which follow criteria
    for(Lead ld : [ SELECT Id, company  FROM lead  limit 50000 ])
    {
		leadids.add(ld.Id);
	}
    //Iterate over all Leads and modify Tasks for them + Add to a list variable 
    for(Lead ld : [SELECT Id, Prospect_notes__c,(SELECT Id, Whatid, Description FROM Tasks) from Lead Where Id IN : leadids])
    
     {
        for(Task t : ld.Tasks) {
        
           if(t.Description==null)
            t.Description = ld.prospect_notes__c;
            taskstoupdate.add(t);
        }
    }
    
    //Update Tasks
    if(taskstoupdate.size()>0) {
        update taskstoupdate;
    }
}
If possible then please update your trigger and fatch only related lead from task not all task

 
Marc D BehrMarc D Behr
The issue is that you are quering for every possible lead in your production instance. As Amit showed, you could add a limit to the query, but I think you really need to consider if you are really trying to update every lead in your system everytime a new task is created. That does not seem right. I think the query needs to be more selective (some where clauses) or, if this is really what you need to do, I would suggest you us a batch job rather than a trigger. Even if you did not hit the row limit, I think you would have hit anotehr limit down the line. Triggers should run quickly, since they are delaying the completition of an operation.  If you had 1 million leads, you need to create 1 million new tasks?
 
Amit Chaudhary 8Amit Chaudhary 8
Yes i am totaly agree with Marc, You should modify your full trigger and need to fatch only select Lead record where from where you need to get Description like below code.
Please below code that will work 
trigger TaskUpdatewithLeadComments on Task (before insert) 
{
	String keyPrefix = Lead.sObjectType.getDescribe().getKeyPrefix();
	Set<ID> setLeadId = new Set<ID>();

	for(Task tsk : Trigger.new)
	{
		if(  tsk.whoId != null && (tsk.whoId).startsWith(keyPrefix)  )
		{
			setLeadId.add(tsk.whoId);
		}
	}
	
	Map<Id,Lead> mapLead = new Map<Id,Lead>([SELECT Id, Prospect_notes__c from Lead Where Id IN : setLeadId ]);
	for(Task tsk : Trigger.new)
	{
		if( tsk.whoId != null &&  (tsk.whoId).startsWith(keyPrefix) && mapLead.containsKey(tsk.whoId) )
		{
		 	Lead led = mapLead.get(tsk.whoId);
            tsk.Description = led.prospect_notes__c;
		}
	}
}


 
Prasanth Reddy MPrasanth Reddy M
Thank You Marc & Amit.

I have modified the trigger by adding additional WHERE clause the trigger is working fine in Production. As SOQL won't support querying long text fields, i created created field, which will be updated with workflow rule, if Prospect Notes (long text) field is not blank.

On the whole what i am trying to achieve is to autopopulate the Prospect Notes field from leads to the task created, if the comments(description) field in task is empty.(The prospect notes will be captured from Pardot in to Salesforce, when a prospect is uploaded/updated)

Any improvizaton in the below trigger is highly appreciated.
  
trigger TaskUpdatewithLeadComments on Task (after insert) {
     Set<Id> leadids = new Set<Id>();
    List<Task> taskstoupdate = new list<Task>();
    
    //Fetch only Leads which follow criteria
    for(Lead ld : [ SELECT Id, company  FROM lead  where Pardot_notes__c='yes'])
        {
            leadids.add(ld.Id);
    }
    //Iterate over all Leads and modify Tasks for them + Add to a list variable 
    for(Lead ld : [SELECT Id, Prospect_notes__c,(SELECT Id, Whatid, Description FROM Tasks) from Lead Where Id IN : leadids])
    
     {
        for(Task t : ld.Tasks) {
        
           if(t.Description==null)
            t.Description = ld.prospect_notes__c;
            taskstoupdate.add(t);
        }
    }
    
    //Update Tasks
    if(taskstoupdate.size()>0) {
        update taskstoupdate;
    }
}

 
Amit Chaudhary 8Amit Chaudhary 8
Where condtion is good and you code will work in production but once Data in Lead Trigger will increase 50K + where Pardot_notes__c='Yes' then same issue come again. That will better if you can change your trigger like below code 
trigger TaskUpdatewithLeadComments on Task (before insert) 
{
	String keyPrefix = Lead.sObjectType.getDescribe().getKeyPrefix();
	Set<ID> setLeadId = new Set<ID>();

	for(Task tsk : Trigger.new)
	{
		if(  tsk.whoId != null && (tsk.whoId).startsWith(keyPrefix)  )
		{
			setLeadId.add(tsk.whoId);
		}
	}
	
	Map<Id,Lead> mapLead = new Map<Id,Lead>([SELECT Id, Prospect_notes__c from Lead Where Id IN : setLeadId ]);
	for(Task tsk : Trigger.new)
	{
		if( tsk.whoId != null &&  (tsk.whoId).startsWith(keyPrefix) && mapLead.containsKey(tsk.whoId) )
		{
		 	Lead led = mapLead.get(tsk.whoId);
            tsk.Description = led.prospect_notes__c;
		}
	}
}

 
This was selected as the best answer
Prasanth Reddy MPrasanth Reddy M
@ Amit - thank you. I just modified my above and few  other triggers to before insert tiggers, they are working like charm.

@  Marc - I will keep your suggestion in mind in my apex learning journey.