0

I have tried an aggregate query, but the result size is too large. So I built a new batch to run over the data and create an aggregation over Account and Product. Condition is that the Revenue/Invoiced Sales should be summed up for all aggregations of account and product, as there is one record for a specific Account and a specific Product each day.

This is the batch:

global with sharing class AggregateSalesDataBatch implements Database.Batchable<sObject>, Database.Stateful{

    global Map<Id, Map<Id, Double>> mapAccountsAndProductsFromBatch = new Map<Id, Map<Id, Double>>();
    global Map<Id, Map<Id, Double>> mapAccountsAndProductsFromAggregated = new Map<Id, Map<Id, Double>>();
    global Map<Id, Map<Id, Id>> mapAccountsAndProductsWithSDAIdFromAggregated = new Map<Id, Map<Id, Id>>();
    global List<Sales_Data_Aggregated__c> listSalesDataAggregated_Upsert = new List<Sales_Data_Aggregated__c>();
    global List<Sales_Data_Aggregated__c> listSalesDataAggregated_Delete = new List<Sales_Data_Aggregated__c>();

    global Database.QueryLocator start(Database.BatchableContext param1){
        //Sales Data selektieren
        return Database.getQueryLocator(
            'SELECT '+
                'Id, Name, Account__c, Account__r.Name, Product__c, Product__r.Name, Invoiced_Sales__c '+
            'FROM ' +
                'Sales_Data__c ' +
            'WHERE ' +
                'Date__c >= LAST_N_MONTHS:6 AND Invoiced_Sales__c > 0'
        );
    }

    global void execute(Database.BatchableContext param1, List<Sales_Data__c> batchSalesData){

        Set<Id> setAccountIds = new Set<Id>();
        for(Sales_Data__c sd : batchSalesData){
            setAccountIds.add(sd.Account__c);
        }

        //Query for all Sales Data Aggregated
        List<Sales_Data_Aggregated__c> listSalesDataAggregated = [SELECT Id, Product__c, Account__c, Revenue_Half_Year__c FROM Sales_Data_Aggregated__c];
        System.debug('listSalesDataAggregated '+listSalesDataAggregated.size());

        //Create a map of Sales Data Aggregated from Batch-Sales Data
        for(Sales_Data__c sd : batchSalesData){
            if(mapAccountsAndProductsFromBatch.get(sd.Account__c) == null){
                mapAccountsAndProductsFromBatch.put(sd.Account__c, new Map<Id, Double>()); 
            }
            if (mapAccountsAndProductsFromBatch.get(sd.Account__c).get(sd.Product__c) == null){
                mapAccountsAndProductsFromBatch.get(sd.Account__c).put(sd.Product__c, sd.Invoiced_Sales__c);
            } else if (mapAccountsAndProductsFromBatch.get(sd.Account__c) != null && mapAccountsAndProductsFromBatch.get(sd.Account__c).get(sd.Product__c) != null){
                Double revenue = mapAccountsAndProductsFromBatch.get(sd.Account__c).get(sd.Product__c);
                revenue += sd.Invoiced_Sales__c;
                mapAccountsAndProductsFromBatch.get(sd.Account__c).put(sd.Product__c,revenue);
            }

        }

        //Create a map of Sales Data Aggregated from Batch-Sales Data
        for(Sales_Data_Aggregated__c sda : listSalesDataAggregated){
            if(mapAccountsAndProductsFromAggregated.get(sda.Account__c) == null){
                mapAccountsAndProductsFromAggregated.put(sda.Account__c, new Map<Id, Double>()); 
                mapAccountsAndProductsWithSDAIdFromAggregated.put(sda.Account__c, new Map<Id, Id>()); 
            }
            if (mapAccountsAndProductsFromAggregated.get(sda.Account__c).get(sda.Product__c) == null){
                mapAccountsAndProductsFromAggregated.get(sda.Account__c).put(sda.Product__c, sda.Revenue_Half_Year__c);
                mapAccountsAndProductsWithSDAIdFromAggregated.get(sda.Account__c).put(sda.Product__c, sda.Id); 
            } 
        }

        for(Id accId : mapAccountsAndProductsFromBatch.keySet()){
            for(Id prodId : mapAccountsAndProductsFromBatch.get(accId).keySet()){
                Sales_Data_Aggregated__c sda = new Sales_Data_Aggregated__c();
                sda.Account__c = accId;
                sda.Product__c = prodId;
                sda.Revenue_Half_Year__c = mapAccountsAndProductsFromBatch.get(accId).get(prodId);
                //Add sda to new list if it is not contained in sda-Query
                if(mapAccountsAndProductsFromAggregated.get(accId) == null || mapAccountsAndProductsFromAggregated.get(accId).get(prodId) == null){
                    listSalesDataAggregated_Upsert.add(sda);
                } else if (mapAccountsAndProductsFromAggregated.get(accId) != null && mapAccountsAndProductsFromAggregated.get(accId).get(prodId) != sda.Revenue_Half_Year__c){
                    //Get existing sda Id
                    sda.Id = mapAccountsAndProductsWithSDAIdFromAggregated.get(accId).get(prodId);
                    listSalesDataAggregated_Upsert.add(sda);
                }
            }
        }
        for(Id accId : mapAccountsAndProductsFromAggregated.keySet()){
            for(Id prodId : mapAccountsAndProductsFromAggregated.get(accId).keySet()){
                if(mapAccountsAndProductsFromBatch.get(accId) == null || mapAccountsAndProductsFromBatch.get(accId).get(prodId) == null){
                    Sales_Data_Aggregated__c sda = new Sales_Data_Aggregated__c();
                    sda.Id = mapAccountsAndProductsWithSDAIdFromAggregated.get(accId).get(prodId);
                    listSalesDataAggregated_Delete.add(sda);
                } 
            }
        }
        System.debug('listSalesDataAggregated_Delete '+listSalesDataAggregated_Delete.size());
        System.debug('listSalesDataAggregated_Upsert '+listSalesDataAggregated_Upsert.size());

        try{
            if(listSalesDataAggregated_Delete.size() > 0){
                delete listSalesDataAggregated_Delete; 
            }
            if(listSalesDataAggregated_Upsert.size() > 0){
                upsert listSalesDataAggregated_Upsert; 
            }
        } catch (Exception e){
            System.debug('Error on DML operation for Product Lisings: '+e.getMessage());
        }

    }

    global void finish(Database.BatchableContext param1){

    }

}

I get the errors "Too many DML rows: 10001" and "Apex CPU time limit exceeded" when listSalesDataAggregated_Upsert gets too large. I need to insert it multiple times though (according to my understanding), because I need to sum up Invoiced Sales.

Any ideas on how to make the code work?

1 Answer 1

2
global List<Sales_Data_Aggregated__c> listSalesDataAggregated_Upsert = new List<Sales_Data_Aggregated__c>();
global List<Sales_Data_Aggregated__c> listSalesDataAggregated_Delete = new List<Sales_Data_Aggregated__c>();

These two variables should be reset for each execution of the execute method. In fact, they should be in the local scope for the method:

global void execute(Database.BatchableContext context, Sales_Data__c[] scope) {
  List<Sales_Data_Aggregated__c> listSalesDataAggregated_Upsert = new List<Sales_Data_Aggregated__c>();
  List<Sales_Data_Aggregated__c> listSalesDataAggregated_Delete = new List<Sales_Data_Aggregated__c>();

P.S. You can make your methods/class public instead of global. This is generally regarded as a best practice.

3
  • Thank you for this suggestion. I tried it, and it worked better than the original code. However, I still get an error "Too many DML rows: 10001" towards the end of the batch. Commented Mar 16, 2021 at 7:54
  • @user40501 You can try reducing scope size (Database.executeBatch(myBatchJob, scopeSize);), or you might consider querying by account, instead, then querying Sales_Data__c inside the execute function, or possibly a combination of the two.
    – sfdcfox
    Commented Mar 16, 2021 at 11:37
  • @sfdxfox: Did restructure the batch so it would query Account in the first place. That was a great suggestion, works *** Commented Mar 16, 2021 at 13:21

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .