Can any one help me on this Batch Job issue: System.LimitException: Too many query rows: 50001

Question

Can any one help me on this issue:

Error:

Debug Log: 35.0 APEX_CODE,DEBUG;APEX_PROFILING,INFO;CALLOUT,INFO;DB,INFO;SYSTEM,DEBUG;VALIDATION,INFO;VISUALFORCE,INFO;WORKFLOW,INFO 09:28:13.141 (141263478)|EXECUTION_STARTED 09:28:13.141 (141316469)|CODE_UNIT_STARTED|[EXTERNAL]|01pK0000000BJee|UpdateClientTeamSharing 09:28:13.158 (158830974)|SOQL_EXECUTE_BEGIN|[49]|Aggregations:0|SELECT Id, Parent.Household__c, UserOrGroupId FROM Client_Insight__Share WHERE Parent.Household__c = :tmpVar1 09:28:16.001 (3001616622)|SOQL_EXECUTE_END|[49]|Rows:50001 09:28:16.001 (3001692173)|EXCEPTION_THROWN|[49]|System.LimitException: Too many query rows: 50001 09:28:16.001 (3001919769)|FATAL_ERROR|System.LimitException: Too many query rows: 50001

Class.UpdateClientTeamSharing.execute: line 49, column 1

Here is the Code:

global class UpdateClientTeamSharing implements database.Batchable<sObject>{


    global Integer intSuccessCount = 0;
    global Integer intErrorCount = 0;   
     global integer totalUserCount = 0;
    private string query;
    public list<Id> lstId;

    global updateClientTeamSharing(){
    list<string> pIds = new list<string>(); 
      list<ProfileIds__c> lstP = ProfileIds__c.getall().values();

      for(ProfileIds__c pro:lstP){
           pIds .add(pro.ProfileId__c);
      }
      map<id,User> userslist= new map<id,user> ([Select id, isactive, User.profile.name from User where isactive=True and user.profileId in :pIds]);
      lstId = new list<Id>();
      lstId.addAll(userslist.keyset());
      string query = 'Select id,User__c,Household__c FROM HouseholdTeamMember__c where isActiveFlag__c = true and User__c in :lstId';    

        this.query = query;
    }

   global Database.QueryLocator start(Database.BatchableContext BC){
        return Database.getQueryLocator(query);
    }

    global void execute(Database.BatchableContext BC, List<sObject> scope){
        Set<id> HHids= new Set<id>();

        for(sObject s:scope){
            HouseholdTeamMember__c htm = (HouseholdTeamMember__c)s;
            HHids.add(htm.Household__c);
        }

        List<Client_Insight__Share> CIS = [SELECT Id, Parent.Household__c, UserOrGroupId FROM Client_Insight__Share
                                         WHERE Parent.Household__c IN :HHids];
        Set<HouseholdTeamMember__c> UpdateHMT= new Set<HouseholdTeamMember__c >();

        for(sObject s:scope){
            totalUserCount++;
        boolean bfound=false;
            HouseholdTeamMember__c htmNew = (HouseholdTeamMember__c)s;
            for(Client_Insight__Share cs : CIS){
                if(htmNew.User__c == cs.UserOrGroupId && htmNew.Household__c == cs.Parent.Household__c){
                bfound=true;
                }
                if(!bfound){
                 UpdateHMT.add(htmNew);
                }
            }
        }
        if(!UpdateHMT.isEmpty()){
        list<HouseholdTeamMember__c> UpdateHMTNew = new list<HouseholdTeamMember__c>();
        UpdateHMTNew.addall(UpdateHMT);
        Database.SaveResult[] lsr = Database.update(UpdateHMTNew, false);

        for(Database.SaveResult sr: lsr)
        {
            if(sr.isSuccess())
            {
                intSuccessCount = intSuccessCount+1;
            }
            else
            {
                intErrorCount = intErrorCount+1;
            }
        }
        system.debug('***intSuccessCount = ' + intSuccessCount);
        system.debug('***intErrorCount = ' + intErrorCount);
        system.debug('****totalCount ='+totalUserCount);
        }


    }
    global void finish(Database.BatchableContext BC){
     //Get results for this job
        SystemLogSvc.generateResultsLog(BC.getJobId(), intSuccessCount, intErrorCount);
        SystemLogSvc.generateResultsLog(BC.getJobId(),  totalUserCount,intSuccessCount );
    }



}

Answer 1

Querying child objects in the execute method is an anti-pattern because you can't predict how many rows will be involved. Instead ensure that the start method directly returns the child objects so you can control the number of records with the batch size.

For your case the query would be something (not too clear on your data model) like this:

public Database.QueryLocator start(Database.BatchableContext bc) {

    list<Id> lstId = ...

    return Database.getQueryLocator([
            SELECT Id, Parent.Household__c, UserOrGroupId
            FROM Client_Insight__Share
            WHERE Parent.Household__r.isActiveFlag__c = true
            AND Parent.Household__r.User__c in :lstId
            ]);
}

and you would have to change your execute method too. In there parent object fields can just be grabbed through the relationship references.

Note that batchables can use static SOQL and don't need to be global; the example in the documentation is less than ideal.

If you need to keep state between batches you can add the marker interface Database.Stateful and uses class fields to hold the data.

Answer 2

Looks like your query:

List<Client_Insight__Share> CIS = [SELECT Id, Parent.Household__c, UserOrGroupId FROM Client_Insight__Share WHERE Parent.Household__c IN :HHids];

Is returning more than 50,000 rows even with your filter using HHids.

Without knowing your underlying data model and business use case, the immediate fix would be to reduce the scope size of the batch from the default to say 200

database.executeBatch([var],200);

With that said, you should review your data model and business case to see if you could optimize or rework what you are trying to do.

In looking at your code you are executing an Update of the Scope records but you never actually change anything. What is the purpose of the update? I do not see this code actually doing anything except update the records if a share it not found but not actually changing anything prior to the update.

What is your goal with this code