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
MachhiMachhi 

Too many SOQL queries: 201 - a mystery

Hi,

 

Has anybody come across below issue with Salesforce?

 

The batch Apex job processed 5 batches with 0 failures.

 

Error I am getting is:

Update failed. First exception on row 0 with id 001Q0000005U7c4IAC; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, trigAccount: execution of BeforeUpdate caused by: System.Exception: Too many SOQL queries: 201 Class.AssetUtility.GetAssetsForAccount: line 109, column 30 Class.AccountUtility.ResetFlagOnUpdate: line 236, column 30 Trigger.Account: line 29, column 17: []

I have traced following things:  

Error record number on which I am getting error: 600

 

Total records to be processed :678

 

Query being used by Batch Apex:select Id, Number_of_Cases__c, Number_of_Contracts__c from Contact

 

This application has simple task to updat account records. Below is the code in the Execute call:

/* Execute : This function performs Batch Update process
	*/
   	global void execute(Database.BatchableContext BC, List<sObject> scope)
   	{
    	/*	Loop through each records list as per the Query paramater.
    		Perform the updates
    	*/
    	List<Account> accountsToUpdate = new List<Account>{};

    	try
    	{
	    	for(sObject s : scope)
	    	{
	    		Account a = (Account)s;
	    		
	    		if(blnIsIncludeNumberOfCases)
	                a.Number_of_Cases__c = [select count() from Case where Account.id = :a.id];     
	            
	            if(blnIsIncludeNumberOfContracts)
	                a.Number_of_Contracts__c = [select count() from Contract where Account.id = :a.id];
	                
	    		AccountsToUpdate.Add(a);
	    		iCountForAccount++;	
	    	}
	    	
	    	//Update all accounts in batch
	    	update AccountsToUpdate;
    	}
    	catch(Exception e)
    	{
    		iErrorRecordNumber = iCountForAccount;
    		strMessage = 'Error with Batch Apex : ' + e.getMessage() + '<br />Error record number: ' + iErrorRecordNumber;
    	}
    		
    } //End of Execute
 
This seems like Salesforce governance limit, bt I need to know how to overcome this. 
  
Please help. 
 
Regards,
Chandrakant M


Best Answer chosen by Admin (Salesforce Developers) 
aalbertaalbert
For starters, you should move SOQL queries outside of FOR loops in the batch apex execute() method. This will drastically reduce the # of queries executed. Then when you update the Accounts , which invoke the account trigger, any queries executed in the trigger also count against the same governor limits that are instantiated for the batch apex execute() method - since its the same apex request. So look at how you can reduce the # of queries executed in both the execute() method and the account trigger.  


All Answers
MachhiMachhi
Hi All, 
 
Also, everything runs fine if I inactivate the trigger on Account. 
 
 
Please help.
 
Regards,
Chandrakant M 


aalbertaalbert
For starters, you should move SOQL queries outside of FOR loops in the batch apex execute() method. This will drastically reduce the # of queries executed. Then when you update the Accounts , which invoke the account trigger, any queries executed in the trigger also count against the same governor limits that are instantiated for the batch apex execute() method - since its the same apex request. So look at how you can reduce the # of queries executed in both the execute() method and the account trigger.  
This was selected as the best answer
MachhiMachhi
Thanks Albert.  Your guidance is very helpful.  I had given up the option of using Batch Apex as there was no significant progress on the issue.  Now I am back with following inputs to the problem: 
  1. In the above code, you cannot escape from calling SOQL query for count() inside the loop. This has to be called inside the FOR loop. This is because; I need to count number of cases and contracts for each account. This needs SOQL query for count() inside loop.
 This Batch Apex is the solution to handle such scenario as it breaks complete list of accounts in the batches each having 200 accounts. I have tried using other alternatives like calling routine asynchronously as well as using web service call. But all solutions are facing this common problem.   If you have any other opinion on this, please share the same.   
  1. The issue seems to be with the Account trigger. I tried to optimize it at great level. Now this has only the necessary queries. Also, most of the standards like calling updates outside the loop have been implemented. 
 After this the count has gone upto 900 records, BUT not more than that. After 900 records, it starts throwing the same error.  Is there a way we can surpass the trigger governance limit when using batch apex? Or inactivate the trigger when we start routine, then again activate the trigger on completion of the routine?  Your suggestions are very helpful in this regard.  Thanks in advance.