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?

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. – Andrea Voggenreiter Mar 16 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 Mar 16 at 11:37
  • @sfdxfox: Did restructure the batch so it would query Account in the first place. That was a great suggestion, works *** – Andrea Voggenreiter Mar 16 at 13:21

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.