1

I am getting Batch Apex Too many SOQL queries: 201 when the class is scheduled for run in developer console.

Batch Apex:

   global class Batch_EmailBlast implements Schedulable,Database.Batchable<sObject>, Database.Stateful {

  String query;
  public string emailBlastId;
  public Email_Blast__c emailBlast;
  public List<Case> listOfEmailsToBeSent;
  public List<Case> listOfDuplicateCases;
  public List<Account> listOfAccountsToBeUpdated;
  public Map<Id,String> mapOfOrgCaseIdToError;
  public Map<Id,String> mapOfDupCaseIdToError;
  public boolean isError;
  global Batch_EmailBlast() {

  }

  global Database.QueryLocator start(Database.BatchableContext BC) {
    return Database.getQueryLocator([SELECT Id, Inquiry_Type__c, Product_type__c, Solution__c,Reason_For_Calling__c,Problem_Category__c, Duplicate_Case_Problem_Category__c,Duplicate_Case_Product_Type__c, Duplicate_Case_Reason_For_Calling__c, Duplicate_Case_Status__c,Web_Product_Business_Group__c, WebProductPLTypeName__c,Age_Hours__c,Language__c, Case_Status__c,Completed_Date__c, status__c, No_Of_Emails_Sent__c, No_Of_Duplicate_Cases__c FROM  Email_Blast__c                                 WHERE status__c =: Label.Email_Blast_Process_Status AND Criteria_Tested__c =:Label.Email_Blast_TestingStatus AND No_Of_Records_Satisfying_Criteria__c >:Decimal.valueOf(Label.Email_Blast_Case_Count_Limit)]);
  }

    global void execute(Database.BatchableContext BC, List<Email_Blast__c> scope) 
    {
        set<String> setOfDuplicateCreiteria = new set<String>();
        set<ID> setOfAccIds = new set<Id>();
        //Get Duplicate cases and cases for which email to be sent

        for(Email_Blast__c emailBlast : scope)
        {
            String businessGroup = emailBlast.Web_Product_Business_Group__c;
            String webProductPLTypeName = emailBlast.WebProductPLTypeName__c;
            Integer age = Integer.valueOf(emailBlast.Age_Hours__c);
            List<String> languageList = new List<String>();
            if(emailBlast.Language__c != null && emailBlast.Language__c.contains(';'))
            {
                List<String> allLangs = emailBlast.Language__c.split(';');
                for(String iterString: allLangs)
                {
                    languageList.add(iterString);
                } 
            }
            else
            {
            languageList.add(emailBlast.Language__c);
            }
            String status = emailBlast.Case_Status__c;

            String caseQuery = 'Select Id, Inquiry_Type__c, Product_type__c, Solution__c, Reason_For_Calling__c, Email_Blast__c, '
                                +'Problem_Category__c,Status,Send_Mass_Email__c,Web_Product_Support_Product__c, Validation_Flag__c ,'
                                +'Account.PersonEmail,AccountId, Account.Mass_Email_Received__c, Account.Last_mass_email_Date__c from Case where '
                                +'Web_Product_Support_Product__r.Business_Group__c =: businessGroup AND '
                                +'WebProductPLTypeName__c =: webProductPLTypeName AND Case_Age_in_Hours__c >: age AND '
                                +'Language__c in: languageList AND Status=: status AND Account.Mass_Email_Received__c = false '
                                +'order by Id, Account.PersonEmail, Case_Age_in_Hours__c DESC ';

            for(Case iterCase : Database.query(caseQuery))
            {
                listOfEmailsToBeSent = new List<Case>();
                listOfDuplicateCases = new List<Case>();    
                mapOfOrgCaseIdToError = new Map<Id,String>();
                mapOfDupCaseIdToError = new Map<Id,String>();
                listOfAccountsToBeUpdated = new List<Account>();

                String criteria = iterCase.Account.PersonEmail +'-'+iterCase.Web_Product_Support_Product__c;
                if(!setOfDuplicateCreiteria.contains(criteria)){
                    setOfDuplicateCreiteria.add(iterCase.Account.PersonEmail +'-'+iterCase.Web_Product_Support_Product__c);
                    iterCase.Send_Mass_Email__c = true;
                    iterCase.Inquiry_Type__c = emailBlast.Inquiry_Type__c;
                    iterCase.Product_type__c = emailBlast.Product_type__c;
                    iterCase.Problem_Category__c = emailBlast.Problem_Category__c;
                    iterCase.Reason_For_Calling__c = emailBlast.Reason_For_Calling__c;
                    iterCase.Solution__c = emailBlast.Solution__c;
                    iterCase.Email_Blast__c = emailBlast.Id;
                    If(Test.isRunningTest() && ProcessEmailBlast.isError)
                    iterCase.Id = null;
                    listOfEmailsToBeSent.add(iterCase);

                }
                else{
                    iterCase.Status = emailBlast.Duplicate_Case_Status__c;
                    iterCase.Product_type__c = emailBlast.Duplicate_Case_Product_Type__c;
                    iterCase.Problem_Category__c = emailBlast.Duplicate_Case_Problem_Category__c;
                    iterCase.Reason_For_Calling__c = emailBlast.Duplicate_Case_Reason_For_Calling__c;
                    iterCase.Email_Blast__c = emailBlast.Id; 
                    If(Test.isRunningTest() && ProcessEmailBlast.isError)
                    iterCase.Id = null;
                    listOfDuplicateCases.add(iterCase);
                }

        if(!listOfEmailsToBeSent.isEmpty()){
        //update listOfEmailsToBeSent;
        List<Database.SaveResult> updateResults = Database.update(listOfEmailsToBeSent, false);
            for(Integer i=0;i<updateResults.size();i++){
                    if (updateResults.get(i).isSuccess()){
                    setOfAccIds.add(listOfEmailsToBeSent.get(i).AccountId);

                    }else if (!updateResults.get(i).isSuccess()){
                    // DML operation failed
                    Database.Error error = updateResults.get(i).getErrors().get(0);
                    String failedDML = error.getMessage();
                    mapOfOrgCaseIdToError.put(listOfEmailsToBeSent.get(i).Id,failedDML);//failed record from the list
                    system.debug('Failed ID'+listOfEmailsToBeSent.get(i).Id);
                    }

            }
        }

        if(!listOfDuplicateCases.isEmpty()){
        //update listOfDuplicateCases;
        List<Database.SaveResult> updateResults = Database.update(listOfDuplicateCases, false);
        for(Integer i=0;i<updateResults.size();i++)
            {
                if (updateResults.get(i).isSuccess()){
                updateResults.get(i).getId();

                }else if (!updateResults.get(i).isSuccess()){
                // DML operation failed
                Database.Error error = updateResults.get(i).getErrors().get(0);
                String failedDML = error.getMessage();
                mapOfDupCaseIdToError.put(listOfDuplicateCases.get(i).Id,failedDML);//failed record from the list
                system.debug('Failed ID'+listOfDuplicateCases.get(i).Id);
                }

            }
        }

        for(Account iterAcc : [select Id, Mass_Email_Received__c, Last_mass_email_Date__c from Account where
                    Id in: setOfAccIds ])
            {
                iterAcc.Mass_Email_Received__c = true;
                iterAcc.Last_mass_email_Date__c = Date.Today();
                listOfAccountsToBeUpdated.add(iterAcc);
            }
        system.debug('listOfAccountsToBeUpdateddd '+listOfAccountsToBeUpdated);
        try{
                if(!listOfAccountsToBeUpdated.isEmpty())
                {
                    update listOfAccountsToBeUpdated;
                }
            }
            catch(Exception e){
                SystemLoggerUtil.logError(e);
            }

            }
            updateEmailBlast(emailBlast);
        }




  }

  global void finish(Database.BatchableContext BC) {

  }

   global void execute(SchedulableContext scon) {
      Database.executeBatch(new Batch_EmailBlast(),1000);
   }

   private void updateEmailBlast(Email_Blast__c emailBlast)
   {
       if(mapOfDupCaseIdToError.keySet().size() > 0){
      String OrgCaseError = 'Case Id,Error Message\n';
      for(Id iterCaseId : mapOfDupCaseIdToError.keySet()){
        OrgCaseError += iterCaseId+','+mapOfDupCaseIdToError.get(iterCaseId)+'\n';
      }
      Attachment dupCaseAttchment = new Attachment();
      dupCaseAttchment.Name = 'Duplicate Case Errors'+ dateTime.Now()+'.csv';
      dupCaseAttchment.contentType = 'text/csv';
      dupCaseAttchment.body = Blob.valueOf(OrgCaseError);
      dupCaseAttchment.ParentId = emailBlast.Id;
      insert dupCaseAttchment;
    }

    if(mapOfOrgCaseIdToError.keySet().size() > 0){
      String OrgCaseError = 'Case Id,Error Message\n';
      for(Id iterCaseId : mapOfOrgCaseIdToError.keySet()){
        OrgCaseError += iterCaseId+','+mapOfOrgCaseIdToError.get(iterCaseId)+'\n';
      }
      Attachment orgCaseAttchment = new Attachment();
      orgCaseAttchment.Name = 'Case Errors'+ dateTime.Now()+'.csv';
      orgCaseAttchment.contentType = 'text/csv';
      orgCaseAttchment.body = Blob.valueOf(OrgCaseError);
      orgCaseAttchment.ParentId = emailBlast.Id;
      insert orgCaseAttchment;
    }

    emailBlast.Completed_Date__c = DateTime.Now();
    if(!mapOfDupCaseIdToError.values().isEmpty() || !mapOfOrgCaseIdToError.values().isEmpty()){
      emailBlast.status__c = 'Partially Completed';
    }
    else 
      emailBlast.status__c = 'Completed';
    emailBlast.No_Of_Emails_Sent__c = listOfEmailsToBeSent.size();
    emailBlast.No_Of_Duplicate_Cases__c = listOfDuplicateCases.size();
    update emailBlast;
   }

}
  • 3
    Simply execute the job with a smaller batch size? You could even have each record get its own execution context by setting the size to 1: Database.executeBatch(new MyBatch(), 1). – Adrian Larson Oct 23 '18 at 15:05
  • @Adrian Larson Batch Size 1 doesnt work. getting System.LimitException: Too many SOQL queries: 201in case trigger as case are updated in the batch class. Any insights on bulkify this code. – Serena Oct 23 '18 at 17:20
4

You are running a single Dynamic SOQL query for every Email_Blast__c in your batch scope:

        String caseQuery = 'Select Id, Inquiry_Type__c, Product_type__c, Solution__c, Reason_For_Calling__c, Email_Blast__c, '
                            +'Problem_Category__c,Status,Send_Mass_Email__c,Web_Product_Support_Product__c, Validation_Flag__c ,'
                            +'Account.PersonEmail,AccountId, Account.Mass_Email_Received__c, Account.Last_mass_email_Date__c from Case where '
                            +'Web_Product_Support_Product__r.Business_Group__c =: businessGroup AND '
                            +'WebProductPLTypeName__c =: webProductPLTypeName AND Case_Age_in_Hours__c >: age AND '
                            +'Language__c in: languageList AND Status=: status AND Account.Mass_Email_Received__c = false '
                            +'order by Id, Account.PersonEmail, Case_Age_in_Hours__c DESC ';

Later, you run a single query against Account:

    for(Account iterAcc : [select Id, Mass_Email_Received__c, Last_mass_email_Date__c from Account where
                Id in: setOfAccIds ])

If you're running with the default 200-record batch size, that's 201 queries right there, which is enough to trip the limit even without any other code that's running in the transaction, like triggers.

You can either reduce your batch size (which will cost you processing speed, but you may be able to get away with cutting it to just under 200 records), or you can work on bulkifying your Dynamic SOQL so that you run it only once for your scope and postprocess your data in Apex.

|improve this answer|||||
  • Reduced the batch size to 1, Even though getting System.LimitException: Too many SOQL queries: 201 in Case Since I am updating the cases. Could you please give some insights how to bulkify this dyanamic SOQL that avoids the limit(Some Corrections). As I am new to the Salesforce. – Serena Oct 23 '18 at 17:17
  • 1
    If you are getting that error while running with a batch size of 1, your Batch code is not the problem. You probably have un-bulkified code elsewhere, perhaps in a Case trigger. – David Reed Oct 23 '18 at 17:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.