I am getting too many SOQL queries 201 error in apex class
I tried to check the no of queries within loop Below is exact error -
11:48:43.9 (2785518121)|FATAL_ERROR|System.LimitException: Too many SOQL queries: 201
Class.GEN_CalculateActToWinScores.calcUserEligible: line 1343, column 1
Class.GEN_ActonFactsScoreUserEligibleBatch.execute: line 74, column 1
11:48:43.9 (2851114444)|CODE_UNIT_FINISHED|GEN_ActonFactsScoreUserEligibleBatch 11:48:43.9 (2852614277)|EXECUTION_FINISHED
Below is the code for method GEN_CalculateActToWinScores.calcUserEligible -
// Method for set user as ready for AoF
public static void calcUserEligible(List<User> usersList ){
List<Act_on_Facts__c> actOnFactDelete = new List<Act_on_Facts__c>();
Set<String> oppOpenStageNameSet = new Set<String>();
oppOpenStageNameSet.add(GEN_Constants.OPPORTUNITY_IDENTIFY);
oppOpenStageNameSet.add(GEN_Constants.OPPORTUNITY_QUALIFY);
oppOpenStageNameSet.add(GEN_Constants.OPPORTUNITY_PROPOSE);
oppOpenStageNameSet.add(GEN_Constants.OPPORTUNITY_NEGOTIATE);
Set<String> oppReadOnlyRecordTypeNameSet = new Set<String>();
oppReadOnlyRecordTypeNameSet.add(GEN_Constants.READONLY_SINGLE_ACCOUNT_OPP_RECORDTYPENAME);
oppReadOnlyRecordTypeNameSet.add(GEN_Constants.READONLY_WON_AND_DONE_OPP_RECORDTYPENAME);
oppReadOnlyRecordTypeNameSet.add(GEN_Constants.READONLY_CHILD_OPP_RECORDTYPENAME);
oppReadOnlyRecordTypeNameSet.add(GEN_Constants.READONLY_MULTI_ACCOUNT_OPP_RECORDTYPENAME);
if(usersList.size() > 0){
List<Id> idList = new List<Id>();
Integer countResult = 0;
List<User> newUserList = new List<User>();
Integer limitQuery; //10000 //2001
Integer limitResult; //2000
if(CSL_ActOnFactsLimits__c.getValues('QUERY_LIMIT') != null){
limitQuery = Integer.valueOf(CSL_ActOnFactsLimits__c.getValues('QUERY_LIMIT').Value__c);
}
if(CSL_ActOnFactsLimits__c.getValues('MAX_QUERY_RESULTS') != null){
limitResult = Integer.valueOf(CSL_ActOnFactsLimits__c.getValues('MAX_QUERY_RESULTS').Value__c);
}
Boolean eligible = true;
for (User userElement : usersList){
String logDetail = ' QUERY_LIMIT:limitQuery: '+limitQuery+' MAX_QUERY_RESULTS:limitResult: '+limitResult;
logDetail += ' userElement.id: '+userElement.id;
eligible = true;
CSH_ActOnFacts_UserEligible__c userEligible = null;
if(CSH_ActOnFacts_UserEligible__c.getValues(userElement.id) != null){
userEligible = CSH_ActOnFacts_UserEligible__c.getValues(userElement.id);
}
CSH_ActOnFacts_UserEligible__c profileEligible = null;
if(CSH_ActOnFacts_UserEligible__c.getValues(userElement.ProfileId) != null){
profileEligible = CSH_ActOnFacts_UserEligible__c.getValues(userElement.ProfileId);
}
List<Act_on_Facts__c> actonfacts = [select id from Act_on_Facts__c where Lookup_User__c = : userElement.id limit 1];
if(userElement.ManagerId == null){
userElement.Line_Manager_Optional__c = true;
userElement.Act_On_Facts_Manager_List__c = null;
}
if(userElement.Default_Macro_Segment__c == null){
userElement.Default_Macro_Segment__c = 'None';
}
if (userElement.IsActive == false){
eligible = false;
}else if(profileEligible != null || userEligible != null) {
eligible = false;
}else{
countResult = [select count() from Account WHERE OwnerId = :userElement.id LIMIT :limitQuery];
logDetail += ' Account.countResult: '+countResult;
if(countResult!=null && countResult > limitResult ){
eligible = false;
}
if(eligible){
if(oppOpenStageNameSet !=null && oppOpenStageNameSet.size()>0 && oppReadOnlyRecordTypeNameSet !=null && oppReadOnlyRecordTypeNameSet.size()>0){
countResult = [select count() from Opportunity WHERE OwnerId = :userElement.id AND StageName IN:oppOpenStageNameSet AND RecordType.DeveloperName NOT IN: oppReadOnlyRecordTypeNameSet LIMIT :limitQuery];
logDetail += ' Opportunity.countResult: '+countResult;
if(countResult!=null && countResult > limitResult ){
eligible = false;
}
}
}
}
if(!eligible){
userElement.Act_on_Facts_Eligible__c = false;
// remove user from Act on Facts
if(actonfacts != null && actonfacts.size()>0){
for(Act_on_Facts__c a : actonfacts){
actOnFactDelete.add(a);
}
}
}else{
userElement.Act_on_Facts_Eligible__c = true;
}
logDetail += ' userElement.Act_on_Facts_Eligible__c: '+userElement.Act_on_Facts_Eligible__c;
ApplicationLog.logEntry(ApplicationLog.SEVERITY_INFO, 'A2WBatch', 'GEN_ActonFactsScoreUserEligibleBatch:'+userElement.id+': ', logDetail);
newUserList.add(userElement);
}
try{
if(newUserList.size()>0){
update newUserList;
system.debug('HC Update- newUserList ' + newUserList);
}
if(actOnFactDelete.size()>0){
delete actOnFactDelete;
system.debug('HC Update- actOnFactDelete ' + actOnFactDelete);
}
}catch (Exception e){
system.debug('Error updating user ' + e);
}
}
}
Code for GEN_ActonFactsScoreUserEligibleBatch.execute-
global void execute(Database.BatchableContext BC, List<sObject> scope){
CSH_A2W_Settings__c a2wCS = CSH_A2W_Settings__c.getInstance();
if(scope != null){
List<User> userList = scope;
if(userList.size()>0){
if(CSL_ActOnFactsLimits__c.getValues('run_userTrigger') != null){
CSL_ActOnFactsLimits__c run_userTrigger = CSL_ActOnFactsLimits__c.getValues('run_userTrigger');
run_userTrigger.Value__c = 'false';
update run_userTrigger;
}
//GEN_CalculateActOnFactsScores.calcUserEligible(userList); //Commented as part of Decommission activity of AoF
if(a2wCS != null && a2wCS.Enabled_in_Batches__c == True){
GEN_CalculateActToWinScores.calcUserEligible(userList);
}
}
}
}
I am trying to analysis what will be the best possible ways to remove these errors or is there any alternate way to implement the same.
All these are queries in a loop:
for (User userElement : usersList){
...
List<Act_on_Facts__c> actonfacts = [select id from Act_on_Facts__c where Lookup_User__c = : userElement.id limit 1];
...
countResult = [select count() from Account WHERE OwnerId = :userElement.id LIMIT :limitQuery];
...
[select count() from Opportunity WHERE OwnerId = :userElement.id AND StageName IN:oppOpenStageNameSet AND RecordType.DeveloperName NOT IN: oppReadOnlyRecordTypeNameSet LIMIT :limitQuery];
As a very quick & dirty solution you can change the batch's size (how many records are passed to each execute). Default is 200.
Call your class with optional parameter Database.executeBatch(new GEN_ActonFactsScoreUserEligibleBatch(), 10); and see if it helps.
"Proper" fix would require some restructuring, taking queries out of the loop, maybe using some Maps where user's id is the key...
If these were custom objects a "pro" Apex developer would cheat, make these queries in one go, pulling user and related lists, something like
SELECT Id,
(SELECT Id FROM Accounts__r LIMIT 1),
(SELECT Id FROM Opportunities__r WHERE ... LIMIT 1),
(SELECT Id FROM Act_On_Facts__r)
FROM User
WHERE Id IN :scope
This won't work here because relation from Account to owner doesn't have a nice name ("Accounts" won't work). You should be still able to do it on the custom object (last subquery in my example, you can call it outside of the loop)
You might still be able to pull something like that off but it'd probably require looking at sharing-related tables... I'd say doable but if you have time to play with it. If you don't - change scope size and call it a day. Will execute bit longer but 1-liner fix is a win in my book.