0

Hello fellow developers,

I need to run an aggregate query on 900K telephone records to add up a field (grouping by phone line id) and record results in the "summaries logbook".

I have tried different approaches but keep getting "First error: Too many query rows: 50001" - I keep hitting a brick wall with anything I try. Would you be able to recommend a solution that works?

Here is what I have tried so far:

global class BibleGeneratorBatchUpdatePrevYear implements Database.Batchable<sObject> {

    global final Date dateStart;
    global final Date dateEnd;

    global BibleGeneratorBatchUpdatePrevYear(Date dStart, Date dEnd){ 
        this.dateStart = dStart; 
        this.dateEnd = dEnd;
    } 

     global Database.QueryLocator start(Database.BatchableContext BC){ 
        return Database.getQueryLocator('Select id, line__c from Bible__c'); 
    } 

    global void execute(Database.BatchableContext BC, List<Bible__c> scope){ 

        Map<Id, AggregateResult> MaplastY = New Map<Id, AggregateResult>([select Line__c Id, sum(number_of_calls__c) totalcalls from detailed_call_data__c where (date__c >= :dateStart.addYears(-1)) and (date__c <= :dateEnd.addYears(-1)) and Line__c != null group by Line__c]); 

        List<Bible__c> bibles = New List<Bible__c>();     

        for(Bible__c b : scope){ 

            if(!MaplastY.isEmpty()){
                b.Number_of_Calls_Previous_Year__c = (Decimal)MaplastY.get(b.Line__c).get('totalcalls');            
            }                        
            bibles.add(b);   
        }   

        try{
            Update bibles;
        }
        catch(DmlException e){
            System.debug('The following exception has occurred trying to create Bible: ' + e.getMessage());
        }     
    }

     global void finish(Database.BatchableContext BC){ 
        AsyncApexJob a = 
            [Select Id, Status, NumberOfErrors, JobItemsProcessed,TotalJobItems,CreatedBy.Email FROM AsyncApexJob WHERE Id = :BC.getJobId()]; 
        System.debug('********: ' + a.Id); 

    } 

}

Another approach was using Iterable pattern:

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

      global BibleAggregateResultIterable(String soql){
        query = soql;
      }

      global Iterator<AggregateResult> Iterator(){
        return new BibleAggregateResultIterator(query);
      }
    }
-------------

    global class BibleAggregateResultIterator implements Iterator<AggregateResult> {
      AggregateResult [] results {get;set;}
      // tracks which result item is returned
      Integer index {get; set;} 

      global BibleAggregateResultIterator(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++];            
      }       
    }


global class BibleSampleAggregateBatch implements Database.Batchable<AggregateResult> {
    global final String dateStart;
    global final String dateEnd;


    global BibleSampleAggregateBatch(Date dStart, Date dEnd){ 

        this.dateStart = DateTime.newInstance(dStart.year(), dstart.month(), dstart.day()).format('YYYY-MM-dd');
        this.dateEnd = DateTime.newInstance(dEnd.year(), dEnd.month(), dEnd.day()).format('YYYY-MM-dd');
    } 

    // The batch job starts
    global Iterable<AggregateResult> start(Database.BatchableContext bc){
        String query = 'select Line__c Id, sum(number_of_calls__c) totalcalls from detailed_call_data__c where date__c >= ' + dateStart + ' and date__c <= '+ dateEnd + ' and Line__c != null group by Line__c';        

        system.debug('qry: '+ query);
        return new BibleAggregateResultIterable(query);
    } 


    // The batch job executes and operates on one batch of records
    global void execute(Database.BatchableContext bc, List<sObject> scope){ 

        //Map<Id, Id> biblMap = new Map<Id, Id>([Select Line__c, Id from Bible__c]);
        Map<Id, Id> biblMap = new Map<Id, Id>();             
        List<Bible__c> bibList = new List<Bible__c>([Select Line__c, Id from Bible__c]);

        for(Bible__c b :bibList){
            biblMap.put(b.Line__c, b.Id);
        }

        List<Bible__c> bibListToUpdate = new List<Bible__c>();

        for(sObject sObj : scope) {
            AggregateResult ar = (AggregateResult)sObj;

            Bible__c b = new Bible__c();

            b.id = biblMap.get((Id)ar.get('Id'));
            b.Number_of_Calls_Previous_Year__c = (Decimal)ar.get('totalcalls');

            bibListToUpdate.add(b);

            System.debug('>>>> COUNT : ' + ar.get('totalcalls'));
        } 

        try{
            update bibListToUpdate; 
        }
        catch(DmlException e){
            System.debug('The following exception has occurred trying to create Bible: ' + e.getMessage());
        }     


    }
    // The batch job finishes
    global void finish(Database.BatchableContext bc){ }
}

either one of these batch classes is meant to be called by another batch class and both approaches work in the sandbox with a small data set. Everything breaks on the production server.

Your recommendations will be really appreciated.


Phil, your response to my previous question was tremendously helpful. Many thanks for that. Here is the solutions that I put in place and it worked for me:

global class BibleGeneratorBatchUpdate implements Database.Batchable<sObject> { 

    global final Date dateStart;
    global final Date dateEnd;

    global BibleGeneratorBatchUpdate(Date dStart, Date dEnd){ 
        this.dateStart = dStart; 
        this.dateEnd = dEnd;
    } 

    global Database.QueryLocator start(Database.BatchableContext BC){ 
        return Database.getQueryLocator('Select id from TI_Contract__c'); 
    } 


    global void execute(Database.BatchableContext BC, List<TI_Contract__c> scope){ 

        Decimal weekInYear = 52.143;

        //   Map<Id, AggregateResult> lastY = new Map<Id, AggregateResult>([(select Line__c Id, sum(number_of_calls__c) totalcalls from detailed_call_data__c where (date__c >= :dateStart.addYears(-1)) and (date__c <= :dateEnd.addYears(-1)) and Line__c != null group by Line__c]);
        //   Map<Id, AggregateResult> lastY1 = new Map<Id, AggregateResult> ([select Line__c Id, sum(number_of_calls__c) totalcalls from detailed_call_data__c where (date__c >= :dateStart.addYears(-2)) and (date__c <= :dateEnd.addYears(-2)) and Line__c != null group by Line__c]);


        List<AggregateResult> queryCurrentYear = [select TI_Contract__c cntr, Line__c line, count(id) recscount, sum(number_of_calls__c) totalcalls, sum(total_costs__c) totalcost, max(ti_contract__r.value__c) value from detailed_call_data__c where (date__c >= :dateStart) and (date__c <= :dateEnd) AND
                                                  ti_contract__c IN :scope GROUP BY TI_Contract__c, Line__c]; 

        List<Bible__c> bibles = New List<Bible__c>();     

        for(AggregateResult ar : queryCurrentYear){ 

            Bible__c b = New Bible__c();
            b.TI_Contract__c = (Id)ar.get('cntr');
            b.Records_Processed__c=(Decimal)ar.get('recscount');
            b.Number_of_Calls__c=(Decimal)ar.get('totalcalls');
            b.Cost_of_calls__c=(Decimal)ar.get('totalcost');
            b.Contract_Value__c=(Decimal)ar.get('value');            
            b.Cost_per_call__c = ((Decimal)ar.get('value') + (Decimal)ar.get('totalcost')) / (Decimal)ar.get('totalcalls');
            b.Data_Extraction_From__c = dateStart;
            b.Data_Extraction_To__c = dateEnd;
            b.Line__c = (Id)ar.get('line');
            //b.Number_of_Calls_Previous_Year__c = 0;//(Decimal)lastY.get((id)ar.get('line')).get('totalcalls');                
           // b.Number_of_Calls_Previous_Year_1__c = 0; //(Decimal)lastY1.get((id)ar.get('line')).get('totalcalls'); 


            bibles.add(b);   
        }   

        try{
            Insert bibles;
        }
        catch(DmlException e){
            System.debug('The following exception has occurred trying to create Bible: ' + e.getMessage());
        }
    } 

    global void finish(Database.BatchableContext BC){ 
        AsyncApexJob a = 
            [Select Id, Status, NumberOfErrors, JobItemsProcessed,TotalJobItems,CreatedBy.Email FROM AsyncApexJob WHERE Id = :BC.getJobId()]; 
        System.debug('********: ' + a.Id); 


        BibleSampleAggregateBatch batch = new BibleSampleAggregateBatch(dateStart, dateEnd);
        Database.executebatch(batch, 200);


       // BibleGeneratorBatchUpdatePrevYear b = new BibleGeneratorBatchUpdatePrevYear(dateStart, dateEnd);
        //database.executeBatch(b);
    } 
}

However, the one thing I did not mention is that I also needed to run a similar aggregate on a specific field form a previous year, and a previous year-1 within the same batch, which I attempted to do with a now commented out:

 Map<Id, AggregateResult> lastY = new Map<Id, AggregateResult>([(select Line__c Id, sum(number_of_calls__c) totalcalls from detailed_call_data__c where (date__c >= :dateStart.addYears(-1)) and (date__c <= :dateEnd.addYears(-1)) and Line__c != null group by Line__c]);

I figured this is not possible so I have created a linked batch to process queries for previous years and it all gone pare-shaped from there onwards. What do you think now about this problem?

Cheers!

  • Any reason why do you query in execute method detailed_call_data__c for all line__c's, and not one's, referenced by Bible__c's from scope? How many detailed_call_data__c are there for Line__c object at top? if it is less then 50k, then batch job with scope size 1 and appropriate filtering for lines should do the calculation – kurunve Nov 13 at 22:29
  • Kurunve, there are 2.6 million records in the detailed_call_data__c and the query I am passing would select 800K records. There are 12K records in the Line__c object. I am using the line__c as a scope because I need to get aggregate results for each line. I am very experienced in Salesforce so I could be making a mistake. – wmin Nov 13 at 22:43
3

It seems to me this is very like your previous question, and likely has the same basic answer...

The issue I see in your first batch is that the aggregate query does not limit the query for "maplastY" to those entries relevant to the bible instances in the scope. That means every call to execute will try to aggregate query over all 900000 telephone records and only restricts this to the specific date range (NB: you could have used Date or Datetime type variables directly in your query bindings without the string format conversion).

Looking at this, the common data is the Line__c. Consider updating your aggregate query handling thus:

Set<Id> lineIds = new Set<Id>();

for (Bible__c bible : scope) {
    if (bible.Line__c != null) {
        lineIds.add(bible.Line__c);
    }
}

if (!lineIds.isEmpty()) {
    Map<Id, AggregateResult> mapLastY = New Map<Id, AggregateResult>([
        SELECT Line__c Id, sum(number_of_calls__c) totalcalls
        FROM detailed_call_data__c
        WHERE Line__c IN :lineIds
            AND (date__c >= :dateStart.addYears(-1))
            AND (date__c <= :dateEnd.addYears(-1))
            GROUP BY Line__c]); 
    ...
}

Note here we build a set of unique line IDs and use that to restrict the aggregate query, hopefully enough that there will be less than 50K rows that contribute to the aggregation.

Remember you can reduce the scope chunk size when you call Database.executeBatch by passing in a second parameter that is the number of Bible__c instances to process in each call to execute - worst case, set this to 1 to restrict the number of Line__c IDs being matched in the aggregate query and therefore further reducing the number of matching rows contributing to the aggregation.

It should be noted that a batch can process up to 50 million rows from its query locator; trying to implement a batch using an iterable that is derived from an SOQL query then restricts you to the maximum number of rows a query permits, which is just 50k - so generally speaking an iterable solution isn't useful when running against queried data.

UPDATE:

If this doesn't work, simply due to numbers, it may be that you need to change tack and consider dropping the use of aggregate queries. Some suggestions of alternatives:

  1. Use trigger processing for Detailed_Call_Data__c to synchronously incrementally modify a total for the Bible/Line and Year it relates to (here you could have a data object, queried or created if necessary, related to the Line and/or Bible and holding the year, in which the total is managed). All processing must be bulkified to ensure you query and create the data objects appropriately.

  2. A batch on Bible that zeros the year totals then chains a batch on Detailed_Call_Data__c that incrementally updates the totals in the Bibles based on the Line__c and Number_of_Calls__c in the Detailed_Call_Data__c.

Both of these only query objects directly, without the use of aggregate queries. This means the calculations can be done via batch(es) using query locators (so you can process up to 50 million matching instances per batch).

  • Phil, once again, many thanks for your response. I have posted additional information in the question box above. – wmin Nov 13 at 22:55
  • Given the numbers of records, I suspect you need to turn this into multiple steps: a batchon Bible__c to zero the counts on the Bibles, a chained batch on call details to increment the counts on the Bibles based on the line relationship... – Phil W Nov 13 at 23:07
  • Phil, this is what I have tried to achieve with my first example: Class BibleGeneratorBatchUpdatePrevYear invoked by Class BibleGeneratorBatchUpdate - alas I get "First error: Too many query rows: 50001" in the second batch. Would you kindly explain in layman's terms how I can run SUM on a field over 900K records in the second batch and have the ability to save the result? I'd be grateful. – wmin Nov 13 at 23:41
  • See the UPDATE in the answer. – Phil W Nov 14 at 7:21
  • Phil, cheers for that! solved the problem with your proposed solution #2, by using a querylocator on the detailed_calls_data. Also, with regards to "(NB: you could have used Date or Datetime type variables directly in your query bindings without the string format conversion)" - i tried that but keep getting errors. Numbers come out as '2019-10-01 00:00:00'. what is the correct way of handling this? – wmin Nov 14 at 16:41

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.