function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Nihar SharmaNihar Sharma 

Getting error of "System.LimitException: Too many query rows: 50001" in apex trigger

Hello,

i am suddenly getting the following error in apex trigger :

"System.LimitException: Too many query rows: 50001"

Here is my apex trigger code :
  
trigger SetAmountfromOTTtoSFDC on Opportunity (Before Insert, Before Update) {
    
    Map <string, Id> AccountTextIdMap = new Map<String,Id>();
    Map <string, Id> ContactTextIdMap = new Map<String,Id>();
        
    
    if(Trigger.IsInsert || Trigger.IsUpdate){
        
        List<Account> AccList = [select Id, Name, AccountId__c from Account];
        for(Account ac : AccList){
            AccountTextIdMap.put(ac.AccountID__c, ac.id);
        }
        
        List<Contact> ConList = [select Id, Contact_ID__c from Contact];
        for(Contact con : ConList){
            ContactTextIdMap.put(con.Contact_ID__c, con.id);
        }
       
        for(Opportunity op : Trigger.New){

            if(op.AdrAccountSearchKey__c != Null && op.AccountId == Null){
                op.AccountId = AccountTextIdMap.get(op.AdrAccountSearchKey__c);
            }
            
            if(op.AdrContactSearchKey__c != Null && op.Contact__c == Null){
                op.Contact__c = ContactTextIdMap.get(op.AdrContactSearchKey__c);
            }
         }
}

I am using this trigger to set up the records which all are coming from other system using synchronization

i think Batch is very useful to this case but i am not sure how to do it, can somebody help me out

Thanks
Best Answer chosen by Nihar Sharma
Amit Chaudhary 8Amit Chaudhary 8
You are getting this issue because you are not adding any filter or limit in your account and contact list. Please select related account and contact in your code like below 
trigger SetAmountfromOTTtoSFDC on Opportunity (Before Insert, Before Update) {
    
    Map <string, Id> AccountTextIdMap = new Map<String,Id>();
    Map <string, Id> ContactTextIdMap = new Map<String,Id>();
        
    
    if(Trigger.IsInsert || Trigger.IsUpdate){

		Set<Id> setSelectedACCID = new Set<ID>();
		Set<Id> setSelectedContID = new Set<ID>();
		
        for(Opportunity op : Trigger.New)
		{
            if(op.AdrAccountSearchKey__c != Null && op.AccountId == Null)
			{
                setSelectedACCID.add(op.AdrAccountSearchKey__c);
            }
            if(op.AdrContactSearchKey__c != Null && op.Contact__c == Null){
                setSelectedContID.add(op.AdrContactSearchKey__c);
            }
		}
			
        List<Account> AccList = [select Id, Name, AccountId__c from Account where AccountId__c in :setSelectedACCID];
        for(Account ac : AccList)
		{
            AccountTextIdMap.put(ac.AccountID__c, ac.id);
        }
        
        List<Contact> ConList = [select Id, Contact_ID__c from Contact where Contact_ID__c in :setSelectedContID ];
        for(Contact con : ConList){
            ContactTextIdMap.put(con.Contact_ID__c, con.id);
        }
       
        for(Opportunity op : Trigger.New)
		{
            if(op.AdrAccountSearchKey__c != Null && op.AccountId == Null)
			{
                op.AccountId = AccountTextIdMap.get(op.AdrAccountSearchKey__c);
            }
            if(op.AdrContactSearchKey__c != Null && op.Contact__c == Null)
			{
                op.Contact__c = ContactTextIdMap.get(op.AdrContactSearchKey__c);
            }
        }
}


Let us know if this will help you
 

All Answers

Alain CabonAlain Cabon
Hello,

How many accounts and contacts? if more than 50,000, you need a queryLocator and a batch (asynchronous for larger CPU time limit).

Governor limits: Total number of records retrieved by SOQL queries 50,000

Two exceptions: queryLocator with scope (50,000,000 records read by max. scope of 2000) and VF page with readOnly="true" (1,000,000 records). Here the only option is the querylocator.

Alain
Nihar SharmaNihar Sharma
Alain,

we have large number of records, let say 2,00,000

Thanks

 
Amit Chaudhary 8Amit Chaudhary 8
You are getting this issue because you are not adding any filter or limit in your account and contact list. Please select related account and contact in your code like below 
trigger SetAmountfromOTTtoSFDC on Opportunity (Before Insert, Before Update) {
    
    Map <string, Id> AccountTextIdMap = new Map<String,Id>();
    Map <string, Id> ContactTextIdMap = new Map<String,Id>();
        
    
    if(Trigger.IsInsert || Trigger.IsUpdate){

		Set<Id> setSelectedACCID = new Set<ID>();
		Set<Id> setSelectedContID = new Set<ID>();
		
        for(Opportunity op : Trigger.New)
		{
            if(op.AdrAccountSearchKey__c != Null && op.AccountId == Null)
			{
                setSelectedACCID.add(op.AdrAccountSearchKey__c);
            }
            if(op.AdrContactSearchKey__c != Null && op.Contact__c == Null){
                setSelectedContID.add(op.AdrContactSearchKey__c);
            }
		}
			
        List<Account> AccList = [select Id, Name, AccountId__c from Account where AccountId__c in :setSelectedACCID];
        for(Account ac : AccList)
		{
            AccountTextIdMap.put(ac.AccountID__c, ac.id);
        }
        
        List<Contact> ConList = [select Id, Contact_ID__c from Contact where Contact_ID__c in :setSelectedContID ];
        for(Contact con : ConList){
            ContactTextIdMap.put(con.Contact_ID__c, con.id);
        }
       
        for(Opportunity op : Trigger.New)
		{
            if(op.AdrAccountSearchKey__c != Null && op.AccountId == Null)
			{
                op.AccountId = AccountTextIdMap.get(op.AdrAccountSearchKey__c);
            }
            if(op.AdrContactSearchKey__c != Null && op.Contact__c == Null)
			{
                op.Contact__c = ContactTextIdMap.get(op.AdrContactSearchKey__c);
            }
        }
}


Let us know if this will help you
 
This was selected as the best answer
Nihar SharmaNihar Sharma
Hi Amit,

Thanks for your valuable time to this question but i used the same logic and worked for me..

i guess you've answered with same logic :)

thanks again !!
Nihar SharmaNihar Sharma
Amit,

Can you please guide me for Batch Apex ? How can i achieve this same logic using Batch Apex ?

Thanks
-Nihar
Alain CabonAlain Cabon
Amit Chaudhary wrote the best answer above excepted if you really want to update all your accounts and contacts (with what? and how?).

A batch is totally useless with the Amit Chaudhary's solution.

The code below cannot work because it lacks filters for the queries. 
List<Account> AccList = [select Id, Name, AccountId__c from Account];
        for(Account ac : AccList){
            AccountTextIdMap.put(ac.AccountID__c, ac.id);
        }
        
        List<Contact> ConList = [select Id, Contact_ID__c from Contact];
        for(Contact con : ConList){
            ContactTextIdMap.put(con.Contact_ID__c, con.id);
        }

Alain
Nihar SharmaNihar Sharma
Ok. 

Thanks Alain :)