+ Start a Discussion
SATHISH REDDY.SATHISH REDDY. 

OpportunityLineItemSchedule calculation are hitting the CPU time limit and aggregate query doesn't support query more

Hello Trailblazers,

I have a visualforce page with 2 date inputs, a table with Oppty's List(as shown) with calculated schedules & custom button. On button click, i'm trying to calculate the lineitemschedules revenue sum by date - per each opportunity. To achieve this, i tried an aggregate soql, however the following exception is thrown. "Aggregate query does not support queryMore(), use LIMIT to restrict the results to a single batch Error"
soqlOliRevSchedStr = 'SELECT sum(Revenue), scheduledate, Opportunitylineitemschedule.Opportunitylineitem.OpportunityId oppId FROM OpportunityLineItemSchedule WHERE Opportunitylineitem.OpportunityId IN: oppIdSet AND Opportunitylineitem.Product2.Name = \'Revenue\'';   
        soqlOliRevSchedStr += ' AND ScheduleDate >=: tempFromDatePicker AND ScheduleDate <=: tempToDatePicker';  
        soqlOliRevSchedStr += ' GROUP BY OpportunityLineItemSchedule.Opportunitylineitem.OpportunityId, scheduledate ORDER BY ScheduleDate ASC';
        for(AggregateResult aggr : Database.query(soqlOliRevSchedStr)){
            oppIdRevAmtMap = aggregateResultProcess(aggr, oppIdRevAmtMap);
        }
        
        public Map<Id, Double> aggregateResultProcess(AggregateResult aggr, Map<Id, Double> tempOppIdAmtMap){
            Id tempOppId = (Id)aggr.get('oppId');
            if(!tempOppIdAmtMap.containsKey(tempOppId)){
                tempOppIdAmtMap.put(tempOppId,(Double)aggr.get('expr0'));
            }else{
                Double temp = tempOppIdAmtMap.get(tempOppId);
                tempOppIdAmtMap.put(tempOppId, temp+(Double)aggr.get('expr0'));
            }
            return tempOppIdAmtMap;
        }

I changed the above aggregate query into a normal soql on Lineitemschedule and looped through all the dates & calculated sum by date per each opportunity. But this throws another exception as shown below. "System.LimitException: Apex CPU time limit exceeded"
 
soqlOliRevSchedStr = 'SELECT Id, Revenue, ScheduleDate, Opportunitylineitem.OpportunityId FROM OpportunityLineItemSchedule WHERE Opportunitylineitem.OpportunityId IN: oppIdSet AND Opportunitylineitem.Product2.Name = \'Revenue\'';
        soqlOliRevSchedStr += ' AND ScheduleDate >=: tempFromDatePicker AND ScheduleDate <=: tempToDatePicker'; 
        soqlOliRevSchedStr += ' ORDER BY ScheduleDate ASC';
        for(OpportunityLineItemSchedule oliSch : Database.query(soqlOliRevSchedStr)){
             oppIdRevAmtMap = aggregateResultProcess(oliSch, oppIdRevAmtMap);       
        }
        public Map<Id, Double> aggregateResultProcess(OpportunityLineItemSchedule tempOliSched, Map<Id, Double> tempOppIdAmtMap){
            Id tempOppId = tempOliSched.OpportunityLineItem.OpportunityId;
            if(!tempOppIdAmtMap.containsKey(tempOppId)){
                tempOppIdAmtMap.put(tempOppId,(Double)tempOliSched.Revenue);
            }else{
                Double temp = tempOppIdAmtMap.get(tempOppId);
                tempOppIdAmtMap.put(tempOppId, temp+(Double)tempOliSched.Revenue);
            }
            return tempOppIdAmtMap;
        }
Dent on the CPU time
User-added imageVFP
User-added imageEither ways the logic/calculations are consuming most of the CPU time & i'm unable to workaround this. Please share your suggestions.

Thank you!
Andrew GAndrew G
Your aggregate error occurs because the Aggregate won't work because basically the Batch Apex needs to grab stuff in batches and the Aggregate can't work because it hasn't seen the next batch.

Reference for suggestions:
https://help.salesforce.com/articleView?id=000333422&type=1&mode=1

Regards
Andrew
AbhishekAbhishek (Salesforce Developers) 
Hi,

Can you check the below blogs which might answer your query,

https://salesforce.stackexchange.com/questions/183466/workbench-aggregate-query-does-not-support-querymore

https://salesforce.stackexchange.com/questions/164734/exceeded-id-limit-aggregate-query-does-not-support-querymore-use-limit-to-re

I hope you find the above information is helpful. If it does, please mark as Best Answer to help others too.

Thanks.