Avoid 'Apex CPU time limit exceeded' with Batch Process

Question

I encountered this error and have trouble with performance tuning.

I tried asyncronous approach, but Apex CPU time limit exceeded occurs continuously. Anybody can make it more efficient?

Here is code and size of each Lists from static method is about 15,000. Please help.

static List<Opportunity> getOppList(){
    return [SELECT ID, StageName, SuccessDate__c FROM Opportunity];
}

static List<Schedule__c> getScheduleList(){
    return [SELECT ID, Opportunity__c, StartTime__c FROM Schedule__c WHERE Result__c = 'Complete'ORDER BY Opportunity__c];
}

global Database.QueryLocator start(Database.BatchableContext BC) {
    query = 'SELECT ID FROM CASE';
    return Database.getQueryLocator(query);
}

global void execute(Database.BatchableContext BC, List<sObject> scope) {
    List<Opportunity> allopps = getOppList();
    List<Schedule__c> schedules = getScheduleList();

    List<Schedule__c> filtered = new List<Schedule__c>();
    Datetime dt;
    Boolean check;
    Boolean status;

    for ( Opportunity opp : allopps ){
        dt = null;
        check = false;
        status = false;
        for (Schedule__c schedule : schedules ){    
            if( check == false && status == true )
                break;

            if( schedule.Opportunity__c == opp.id ){
                check = true;
                filtered.add(schedule);
                if( dt == null || schedule.StartTime__c < dt)
                    dt = schedule.StartTime__c;

                i++;
            }
            else if( check == true && status == false){
                check = false;
                status = true;
            }
        }

        if( filtered.size() >= 1){
            opp.SuccessDate__c = dt;
            opp.StageName = 'Success';
        }
        else if( filtered.size() == 0 && opp.StageName == 'Success'){
            opp.SuccessDate__c = null;
            opp.StageName = 'Initial Stage';
        }
    }

    System.debug('schedule size : ' + schedules.size());
    System.debug('size : ' + allopps.size() + ' count : ' + i);
    /*
    try{
        update allopps;
    }catch( Exception e){
        System.debug('Error : ' + e.getMessage());
    }
    */
}

Answer 1

You need to rework how the batch is processing the records.

Firstly, base your batch getQueryLocator on the either the Opportunity or Schedule__c records. There doesn't appear to be any reason to bring Case records in here as you don't use the scope for anything.

Next, limit the second query to just those records applicable to the first. So, say your query locator is running through all the Opportunity records. You could then have the SOQL query for Schedule__c records look for only the opportunity records that in in scope.

---

A very rough example:

public Database.QueryLocator start(Database.BatchableContext BC) {
    query = 'SELECT ID, StageName, SuccessDate__c FROM Opportunity';
    return Database.getQueryLocator(query);
}

public void execute(Database.BatchableContext BC, List<Opportunity> scope) {
    Map<Id, Opportunity> oppMap = new Map<Id, Opportunity>(scope);

    List<Schedule__c> schedules = ;

    // Build up a map form the Schedule__c.Opportunity__c to the Schedule__c records
    Map<Id, List<Schedule__c>> opportunityIdToScheduleMap = new Map<Id, List<Schedule__c>>();
    for(Schedule__c schedule : [SELECT ID, Opportunity__c, StartTime__c FROM Schedule__c WHERE Result__c = 'Complete' and Opportunity__c in :oppMap.keySet() ORDER BY Opportunity__c]) {
    {
        Id oppId = schedule.Opportunity__c;
        List<Schedule__c> schedulesForOpp = null;
        if(opportunityIdToScheduleMap.containsKey(oppId)) {
            // Could check for  schedule.StartTime__c < dt here and just keep one.
            schedulesForOpp = opportunityIdToScheduleMap.get(oppId);
        } else {
            schedulesForOpp = new List<Schedule__c>();
            opportunityIdToScheduleMap.put(oppId, schedulesForOpp);
        }
        schedulesForOpp.add(schedule);
    }

    List<Opportunity> toUpdate = new List<Opportunity>();

    // Loop through the Opportunities in scope
    for(Opportunity opp : scope) {
        // Checking the schedule map to see if they are scheduled (or not)
        if(opportunityIdToScheduleMap.containsKey(opp.Id)) {
            List<Schedule__c> schedulesForOpp = opportunityIdToScheduleMap.containsKey(opp.Id);
            // Loop over records to find the lowest  schedule.StartTime__c < dt
            // ...

            // I've just grabbed the first. Finding the lowest startTime from the list should be a quick job.
            opp.SuccessDate__c = schedulesForOpp[0].StartTime__c;
            opp.StageName = 'Success';
            toUpdate.add(opp);
        } else if (opp.StageName == 'Success') {
            opp.SuccessDate__c = null;
            opp.StageName = 'Initial Stage';
            toUpdate.add(opp);
        }

    }

    // Update the scoped Opportunities as required
    update toUpdate;
}

You could probably get smarter with the SOQL queries, but this should generally scale much better.

---

Also, another massive benefit of batches is the ability to define how big the scope should be. This can be done with the optional scope parameter on Database.executeBatch();

Answer 2

First Understand, Why you are getting 'Apex CPU time limit exceeded' with Batch Process.

1. Salesforce has a timeout limit for transactions based on CPU usage. If transactions consume too much CPU time, we'll shut them down as a long-running transaction.

Salesforce counts almost everything else that happens on the app server, including declarative actions. If DML in your code encounters a validation rule with a formula, we'll count the time spent evaluating that formula. CPU time is calculated for all executions on the Salesforce application servers occurring in one Apex transaction—for the executing Apex code, and any processes that are called from this code, such as package code and workflows. CPU time is private for a transaction and is isolated from other transactions. Operations that don’t consume application server CPU time aren’t counted toward CPU time.

2. What's counted

   • All Apex code
   • Library functions exposed in Apex
   • Workflow execution

3. What's not counted

   • Database operations, e.g. DML, SOQL
   • SOSL
   • HTTP callouts

You will get more Information here:-Code more efficiently to avoid 'Apex CPU time limit exceeded'

In Your Case, getOppList and getScheduleList method being called for every scope. Suppose that after running the class, total batch is 1000, so these two methods will be called 1000 times. You can avoid it.

Second, You are using nested for loops, You should avoid it. As I can see, For each scope you are doing nested for loop. let say, your getOppList returns 40000 records and getScheduleList returns 20000 records and your total number of the batch is 1000. Every scope you are executing logic 40000 x 20000 times.

@DanielBallinger has suggested the optimized code, so go through it.

Note:- Database.getQueryLocator will return you 5000000 records at a time, while normal query will return you 50000 only.

Answer 3

As @Daniel suggested, you will have to rework the batch.

1. Use the start method to query records that you will be processing in the execute method. (In your case the query in the start method should either be on Opportunity or on Schedule__c)
2. Try to avoid usage of for loop inside for loop unless it is actually needed.

---

Hope this helps.

global Database.QueryLocator start(Database.BatchableContext BC) {
    query = 'SELECT ID, Opportunity__c, StartTime__c FROM Schedule__c WHERE Result__c = \'Complete\'';
    return Database.getQueryLocator(query);
}

global void execute(Database.BatchableContext BC, List<Schedule__c> scope) {
    List<Opportunity> opportunityList = new List<Opportunity>();
    Set<Id> opportunityIdSet = new Set<Id>();

    //Fetch the opportunity__c value from the scope into a set.
    for(Schedule__c scheduleObj : scope){
        opportunityIdSet.add(scheduleObj.Opportunity__c);
    }

    //Query Opportunities where the Id is IN opportunityIdSet
    opportunityList = [SELECT Id FROM Opportunity WHERE Id IN :opportunityIdSet];

    if(!opportunityList.isEmpty()){
        // Iterate over the opportunityList
    }
}

---

Visit the salesforce documentation for detailed information about using batch apex.