System.LimitException: Too many query rows: 50001 when Using AggregateResult interface in batch class?

Question

I want to aggrgate Average of Quality Score of Account object based on User's Market and I want to those summary details in one of Custom Object.

I have written a batch class where I am using Iterator and Itetable interface. This works fine when we have less records but it throws an error and hits the governor limit somehow.

System.LimitException: Too many query rows: 50001

Iteartor Class:

global class AccQualitySnapshotIterator implements Iterator<AggregateResult>{
    AggregateResult [] results {get;set;}
  // tracks which result item is returned
  Integer index {get; set;} 
       
  global AccQualitySnapshotIterator(String query) {
    index = 0;
    results = Database.query(query);            
  } 
  
  global boolean hasNext(){ 
    return results != null && !results.isEmpty() && index < results.size(); 
  }    
  
  global AggregateResult next(){        
    return results[index++];            
  }       
  }

Iteable Class:

global class AccQualitySnapshotIterable implements Iterable<AggregateResult>{
    private String query;

    global AccQualitySnapshotIterable(String soql){
      query = soql;
    }
  
    global Iterator<AggregateResult> Iterator(){
      return new AccQualitySnapshotIterator(query);
    }
}

Batch class:

global class AccountDataQualitySnapshotBatch implements  Database.Batchable<AggregateResult>, Database.Stateful {
    public List<LOG_LogMessage__c> errrorLogLists = new List<LOG_LogMessage__c>();
    global Iterable<AggregateResult> start(Database.BatchableContext bc){
        
        String query = 'SELECT Owner.Market__c salesmarket, COUNT(Id) recordCount, AVG(Quality_Score__c) avgScore FROM Account GROUP BY Owner.Market__c';
        return new AccQualitySnapshotIterable(query);
    }
    global void execute(Database.BatchableContext bc, List<sObject> scope){ 
        Map<String, List<Data_Quality_Snapshot__c>> salesMarketAccountsMap = new Map<String, List<Data_Quality_Snapshot__c>>();
        Map<String,Data_Quality_Snapshot__c> existingSnpsMap = new Map<String,Data_Quality_Snapshot__c>();
        List<Data_Quality_Snapshot__c> recordsToInsertsOrUpdate = new List<Data_Quality_Snapshot__c>();
        // Query related Account records for each Sales Market
        for(Data_Quality_Snapshot__c existingSnp : [Select Id,Market__c,Quality_Score__c,Number_of_records__c,Current_date__c from Data_Quality_Snapshot__c]){
            existingSnpsMap.put(existingSnp.Market__c,existingSnp);
        }
        
        for(sObject sObj : scope) {
            AggregateResult ar = (AggregateResult)sObj;
            String salesMarket = (String)ar.get('salesmarket');
            Decimal avgScore = (Decimal)ar.get('avgScore');
            Integer recordCount = (Integer)ar.get('recordCount');
            
            Data_Quality_Snapshot__c snapshot = new Data_Quality_Snapshot__c();
            snapshot.Name = !String.isBlank(salesMarket) ? salesMarket : 'None';
            snapshot.Market__c = salesMarket;
            snapshot.Quality_Score__c = avgScore;
            snapshot.Number_of_records__c = recordCount;
            snapshot.Current_date__c = System.Today();
            
            if(!existingSnpsMap.isEmpty() ){
                snapshot.Id = existingSnpsMap.get(salesMarket).Id;
            }
            recordsToInsertsOrUpdate.add(snapshot);
        }
        
        if(!recordsToInsertsOrUpdate.isEmpty()){
            system.debug('Values are:'+recordsToInsertsOrUpdate);
            Database.UpsertResult[] saveResults = Database.upsert(recordsToInsertsOrUpdate,false);
            
        }
    }
    
    global void finish(Database.BatchableContext bc) {
    }
}

Can someone please help what needs to be modified in the code?

Answer 1

The start method supports more than 50k limit only if you use QueryLocator. When you use aggregate result in batch, we hit the normal limit. Instead of using Aggregate Result, you can try below code. Use Database.Stateful to get total record count, get the sum of all records based on Sales Market and then calculate the average.

global class AccountDataQualitySnapshotBatch implements Database.Batchable<sObject>,Database.Stateful {
    public Map<String, Decimal> MapOfSumScore = new Map<String, Decimal>();
    public Map<String, Integer> MapOfRecCount = new Map<String, Integer>();

    global Database.QueryLocator start(Database.BatchableContext BC) {
        return Database.getQueryLocator([SELECT Id FROM Account]); // add some conditions in where clause if any
    }

    global void execute(Database.BatchableContext bc, List<sObject> scope) {
        Map<String, List<Data_Quality_Snapshot__c >> salesMarketAccountsMap = new Map<String, List<Data_Quality_Snapshot__c >>();
        Map<String, Data_Quality_Snapshot__c > existingSnpsMap = new Map<String, Data_Quality_Snapshot__c >();
        List<Data_Quality_Snapshot__c > reccToInsertsOrUpdate = new List<Data_Quality_Snapshot__c >();

        for (Data_Quality_Snapshot__c  existingSnp : [SELECT Id, Market__c, Quality_Score__c, Number_of_records__c, Current_date__c FROM Data_Quality_Snapshot__c ]) {
            existingSnpsMap.put(existingSnp.Market__c, existingSnp);
        }

        
        for (AggregateResult ar : [SELECT Owner.Market__c salesmarket, COUNT(Id) recordCount, SUM(Data_Quality_Score__c) sumScore FROM Account WHERE Id IN :scope GROUP BY Owner.Market__c]) {
            String salesMarket = (String) ar.get('salesmarket');
            Decimal sumScore = (Decimal) ar.get('sumScore'); // Use Sum instead of Average. 
            Integer recordCount = (Integer) ar.get('recordCount'); // Get the total record Count

            if (MapOfSumScore.containsKey(salesMarket)) {
                sumScore += MapOfSumScore.get(salesMarket);
                MapOfSumScore.put(salesMarket, sumScore);
            }
            else{
                MapOfSumScore.put(salesMarket, sumScore);
            }
            if (MapOfRecCount.containsKey(salesMarket)) {
                recordCount += MapOfRecCount.get(salesMarket);
                MapOfRecCount.put(salesMarket, recordCount);
            }
           else {
                MapOfRecCount.put(salesMarket, recordCount);  
            }
            MapOfRecCount.put(salesMarket, recordCount);
        }

        for (String salesMarket : MapOfSumScore.keySet()) {
            Integer recordCount = MapOfRecCount.get(salesMarket);
            Decimal avgScore = (MapOfSumScore.get(salesMarket)/recordCount).round(System.RoundingMode.HALF_UP); // Get the average based on Sum of al record counts.
            Data_Quality_Snapshot__c  snapshot = new Data_Quality_Snapshot__c ();
            snapshot.Name = !String.isBlank(salesMarket) ? salesMarket : 'None';
            snapshot.Market__c = salesMarket;
            snapshot.Quality_Score__c = avgScore;
            snapshot.Number_of_records__c = recordCount;
            snapshot.Current_date__c = System.Today();

            if (existingSnpsMap.containsKey(salesMarket)) {
                snapshot.Id = existingSnpsMap.get(salesMarket).Id;
            }
            reccToInsertsOrUpdate.add(snapshot);
        }

        if(!reccToInsertsOrUpdate.isEmpty()){
            Database.UpsertResult[] saveResults = Database.upsert(reccToInsertsOrUpdate,false);
        }
    }

    global void finish(Database.BatchableContext bc) {
    }
}