3

I am getting

Too many query rows: 50001

when i run the batch apex.

Below code is part of batch apex (FInish Method).

Also there is a soql inside for loop .can someone help me fixing this.

    list<Id> terrIdList = new list<Id>();
    Map<Id,Id> groupIdAndTerrIdMap = new Map<Id,Id>();

    Map<Id,Set<Id>> terrIdAndSetOfNonTargetPractionerAccIdMap  = new Map<Id,Set<Id>>();
    Map<Id,Set<Id>> terrIdAndSetOfTargetPractionerAccIdMap  = new Map<Id,Set<Id>>();
    Map<Id,Set<Id>> terrIdAndSetOfPharmacyAccIdMap = new Map<Id,Set<Id>>();


     for(Account_Statistic__c accStatic : districtAccList){

       Id tID = Id.valueOf(accStatic.Territory_Id__c);
       terrIdList.add(tId); 
    }

    list<Territory> tlist = new list<Territory>();
    tlist = [Select t.ParentTerritoryId, t.Id From Territory t where t.ParentTerritoryId in:terrIdList];

    map<Id,list<Territory>> tmap = new map<Id,list<Territory>>{};

    for(Territory t:tlist){
      if(tmap.containsKey(t.ParentTerritoryId)){
      tmap.get(t.ParentTerritoryId).add(t);
      }else{
      list<Territory> plist = new list<Territory>();
      plist.add(t);
      tmap.put(t.ParentTerritoryId,plist);
      }
    }

    list<Territory>relatedlist= new list<Territory>();
    for(Account_Statistic__c accStaticRec:districtAccList)
    { 
     Set<Id> finalaccIdSet = new Set<Id>();
     Set<Id> totalaccIdSet = new Set<id>();        
     relatedlist = tmap.get(accStaticRec.Territory_Id__c);
     for(Territory terr:relatedlist){

        for(Group groupRec : [SELECT Id,RelatedId FROM Group WHERE RelatedId =:terr.Id] ){
            groupIdAndTerrIdMap.put(groupRec.Id,groupRec.RelatedId );         
        }

     if(groupIdAndTerrIdMap != NULL && groupIdAndTerrIdMap.size() > 0){

        for(AccountShare accShare : [SELECT Id,AccountId,Account.RecordTypeId,Account.RecordType.Name,Account.Target__c, UserOrGroupId FROM AccountShare WHERE (RowCause = 'Territory' OR RowCause = 'TerritoryManual') AND UserOrGroupId IN :groupIdAndTerrIdMap.keySet() AND (Account.RecordType.Name = 'Practitioner' OR Account.RecordType.Name = 'Pharmacy')] ){

             if(accShare.Account.RecordType.Name == 'Practitioner'){
                 if(accShare.Account.Target__c){
                    Id RelatedId = groupIdAndTerrIdMap.get(accShare.UserOrGroupId );

                    if(!terrIdAndSetOfTargetPractionerAccIdMap.containsKey(RelatedId) )
                    terrIdAndSetOfTargetPractionerAccIdMap.put(RelatedId,new Set<Id>());

                    terrIdAndSetOfTargetPractionerAccIdMap.get(RelatedId).add(accShare.AccountId);
                }else{
                    Id RelatedId = groupIdAndTerrIdMap.get(accShare.UserOrGroupId );

                    if(!terrIdAndSetOfNonTargetPractionerAccIdMap.containsKey(RelatedId) )
                    terrIdAndSetOfNonTargetPractionerAccIdMap.put(RelatedId,new Set<Id>());

                    terrIdAndSetOfNonTargetPractionerAccIdMap.get(RelatedId).add(accShare.AccountId);    
                }
            }else if(accShare.Account.RecordType.Name == 'Pharmacy'){
                Id RelatedId = groupIdAndTerrIdMap.get(accShare.UserOrGroupId );

                if(!terrIdAndSetOfPharmacyAccIdMap.containsKey(RelatedId) )
                terrIdAndSetOfPharmacyAccIdMap.put(RelatedId,new Set<Id>());

                terrIdAndSetOfPharmacyAccIdMap.get(RelatedId).add(accShare.AccountId);    
            }

        }
    } 

    Set<Id> accIdSet = new Set<Id>();
    Set<Id> accSet = new Set<Id>();
    if(accStaticRec.Account_Type__c == 'Target'){
        accIdSet = terrIdAndSetOfTargetPractionerAccIdMap.get(accStaticRec.Territory_Id__c);
    }else if(accStaticRec.Account_Type__c == 'Non-Target'){
        accIdSet = terrIdAndSetOfNonTargetPractionerAccIdMap.get(accStaticRec.Territory_Id__c);
    }else if(accStaticRec.Account_Type__c == 'Pharmacy'){
        accIdSet = terrIdAndSetOfPharmacyAccIdMap.get(accStaticRec.Territory_Id__c);
    }

    if(accIdSet != NULL && accIdSet.size() > 0){

        AggregateResult[] groupedResultCall = [SELECT Account_vod__c,count(Id) FROM Call2_vod__c WHERE Call_Date_vod__c >= :MonthStartDate  AND Call_Date_vod__c <= :MonthEndDate  AND Account_Type__c =:accStaticRec.Account_Type__c 
                                                    AND Territory_vod__c =:terr.Id AND Status_vod__c = 'Submitted_vod' AND (Call_Type_vod__c != 'Call Only' OR Call_Type_vod__c != 'Event Only' OR Call_Type_vod__c != 'Event Detail')
                                                    AND Account_vod__c IN :accIdSet  
                                                    //PS added 7.11.2013
                                                    AND Activity_Type__c <> 'Staff'
                                                    GROUP BY Account_vod__c ];

        System.debug('::::groupedResultCall:::::'+groupedResultCall);
        Map<Id,Integer> AccIdAndCountOfCallMap = new Map<Id,Integer>();
        Integer totalCallsCount = 0;

        for (AggregateResult ar : groupedResultCall)  {
            String accId  = String.ValueOf(ar.get('Account_vod__c'));
            accSet.add(Id.valueOf(accId));
            finalaccIdSet.addAll(accSet);
          }
         totalaccIdSet.addAll(accIdSet); 
       }
       System.debug('::::finalaccIdSet:::::'+finalaccIdSet.size());
       System.debug('::::totalaccIdSet:::::'+totalaccIdSet.size());
     }
      System.debug('::::finalaccIdSet:::::'+finalaccIdSet.size());
      System.debug('::::totalaccIdSet:::::'+totalaccIdSet.size());

    if(accStaticRec.Reach_Status__c == 'Reached'){
        accStaticRec.Accounts__c = finalaccIdSet.size(); 
     }else if(accStaticRec.Reach_Status__c == 'Not Reached'){
            if(finalaccIdSet.size() == totalaccIdSet.size()){
                accStaticRec.Accounts__c = 0;
            }else{
                accStaticRec.Accounts__c = totalaccIdSet.size() - finalaccIdSet.size();
            }
         }
     accStaticListUpdate.add(accStaticRec);
        String mapKey = accStaticRec.Territory_Id__c+accStaticRec.Account_Type__c+accStaticRec.Reach_Status__c;
        ProcessedDistrictMap.put(mapKey,new Map<String,Decimal>());
        ProcessedDistrictMap.get(mapKey).put('#Accounts',accStaticRec.Accounts__c);

    } 
4

Try cutting your batch size down. Make sure you process records in the execute method.

------- EDIT -------

global void execute(Database.BatchableContext BC, List<sObject> scope){
    // process records here
    // only query records that pertain to the scope
}

global void finish(Database.BatchableContext BC){
    // the finish method is to do things once all the batches are complete
    // for example, send an email describing how the batch process went
}

http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_batch_interface.htm

The Database.executeBatch method takes two parameters: An instance of a class that implements the Database.Batchable interface. The Database.executeBatch method takes an optional parameter scope. This parameter specifies the number of records that should be passed into the execute method. Use this parameter when you have many operations for each record being passed in and are running into governor limits. By limiting the number of records, you are thereby limiting the operations per transaction. This value must be greater than zero. If the start method returns a QueryLocator, the optional scope parameter of Database.executeBatch can have a maximum value of 2,000. If set to a higher value, Salesforce chunks the records returned by the QueryLocator into smaller batches of up to 2,000 records. If the start method returns an iterable, the scope parameter value has no upper limit; however, if you use a very high number, you may run into other limits.

One has to be very careful when using a SOQL query in a loop to make sure you don't run into the TOO MANY SOQL QUERIES error -- you need a guarantee that the outer loop has a sufficiently small number of records to iterate over. And using some clever scheme with maps can get the SOQL query out of the loop. But you're not running into this problem (yet). The issue is the current batch size (default = 200) requires you to query too many records. Two solutions:

  1. Lower the batch size (to 100 or 50) until it works. But try to have an estimate of how many records you'll query given a certain batch size so you know you won't run into errors at runtime (now and in the future).
  2. You may be querying non-related records. Try refining your queries to only query records that are necessary.

-------- EDIT #2 ------

Here's an example of a method I use to send a nice email describing a batch process to be used in its finish method

public class util{

    public static Messaging.SingleEmailMessage finishEmail(Database.BatchableContext BC){
        messaging.singleEmailMessage mail = new messaging.singleEmailMessage();
        AsyncApexJob a = [SELECT Id,
                                 Status,
                                 NumberOfErrors,
                                 JobItemsProcessed,
                                 TotalJobItems,
                                 CompletedDate,
                                 ExtendedStatus,
                                 ApexClass.name,
                                 CreatedBy.Email,
                                 CreatedBy.Name
                          FROM AsyncApexJob 
                          WHERE Id =:BC.getJobId()];
        mail.setToAddresses(new string[]{a.CreatedBy.Email});
        mail.setReplyTo('yourEmail@someDomain.com');
        mail.setSubject('Batch Code Complete -- '+a.ApexClass.name);
        mail.setUseSignature(false);

        string td1='"border:1px solid green; width=200px;"';
        string td2='"width=200px; border:1px solid green; background-color:red; color:white; font-weight:bold;"';
        string tdHead='"border:1px solid green; width=200px; color:white; background-color:green; font-weight:bold;"';
        string ExtendedStatus='';
        if(a.ExtendedStatus!=null)
            ExtendedStatus=a.ExtendedStatus;
        string tdErrors=td1;
        if(a.NumberOfErrors>0)
            tdErrors=td2;
        string htmlBody = '<div style="border:2px solid green; border-radius:15px;"><p>Hi,</p><p><span style="color:brown; font-weight:bolder;">Salesforce</span> completed running <b>Apex Batch Code<>/b>.</p>'
        +'<p>Results:</p>'
        +'<center><table style="border:3px solid green; border-collapse:collapse;">'
        +'<tr><td style='+tdHead+'>Class Name</td><td style='+tdHead+'>'+a.ApexClass.name+'</td></tr>'
        +'<tr><td style='+td1+'>Completed Date</td><td style='+td1+'>'+a.CompletedDate+'</td></tr>'
        +'<tr><td style='+td1+'>Status</td><td style='+td1+'>'+a.Status+'</td></tr>'
        +'<tr><td style='+td1+'>Job Items Processed</td><td style='+td1+'>'+a.JobItemsProcessed+' / '+a.TotalJobItems+'</td></tr>'
        +'<tr><td style='+td1+'>NumberOfErrors</td><td style='+tdErrors+'>'+a.NumberOfErrors+'</td></tr>'
        +'<tr><td style='+td1+'>Extended Status</td><td style='+td1+'>'+ExtendedStatus+'</td></tr>'
        +'<tr><td style='+tdHead+'>Created By</td><td style='+tdHead+'>'+a.CreatedBy.Name+' ('+a.CreatedBy.Email+')</td></tr>'
        +'</table></center>'
        +'<p>Enjoy the <b>results</b>!</p><p><span style="font-family:"Courier New", Courier, monospace; color:green; font-weight:bold; font-size:larger;">Scott</span></p></div>';

        mail.setHtmlBody(htmlBody);
        return mail;
    }//END finishEmail

}//END class

Test code:

@isTest
public class Util_test{

    @isTest private static void testFinishEmail_Batch(){
        list<Account> accts=new list<Account>();
        for(integer i=0;i<(math.round(math.random()*200)+1);i++)
        accts.add(new Account(Name='test'+string.valueof(i)));
        insert accts;
        test.startTest();
        id batchInstance=database.executeBatch(new test_Batch());
        test.stopTest();
    }//END testFinishEmail_Batch
}

With test_Batch_code:

global class test_Batch implements database.Batchable<sObject>{
    global final string query;

    global test_Batch(){
        query='SELECT id FROM Account';
    }//END test

    global database.QueryLocator start(database.BatchableContext BC){
        return database.getQueryLocator(query);
    }//END start

    global void execute(database.BatchableContext BC,list<sObject> scope){
    }//END execute

    global void finish(database.BatchableContext BC){
        Util.finishEmail(BC);  // <----  ** The finishEmail method **
    }//END finish

}//END test_Batch
| improve this answer | |
  • my records are processing in the finish method – sfdc Aug 14 '13 at 15:21
  • 2
    make sure you process the records in the execute method -- that's where you can process only records in the batch (i.e. scope) – Scott Pelak Aug 14 '13 at 15:26
  • I added an edit – Scott Pelak Aug 14 '13 at 15:29
  • I am still getting an error after i moved to execute method.. – sfdc Aug 14 '13 at 19:38
  • 1
    I fixed the error :Too Many rows 50001. Now i have another issue with the code .The aggregate result is returning 0 rows. – sfdc Aug 15 '13 at 18:43

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.