CPU Apex Limit Error Exception

Question

How to optimize this code ,I am continuously getting "Apex CPU Limit Error" Can anyone help me out with optimize solution Here is the code :- Batch Size :50 passing here are some number of records we have opportunities: 1 million records. opportunity history: 1 million records.

global class LastStateUpdatedBatch implements Database.Batchable<sObject> {

    String query;

    global LastStateUpdatedBatch() {
        query = 'Select Last_Stage_Update__c, (Select CreatedDate, StageName From OpportunityHistories order by CreatedDate desc) From Opportunity where Last_Stage_Update__c = null AND IsClosed = false ';
    }


    global Database.QueryLocator start(Database.BatchableContext BC) {
        return Database.getQueryLocator(query);
    }

    global void execute(Database.BatchableContext BC, List<sObject> scope) {
    List<Opportunity> listOfOldOpp = (List<Opportunity>)scope;
    for( Opportunity opp : listOfOldOpp){
        String lastOppStage = opp.OpportunityHistories[0].StageName;
        DateTime lastUpdate = opp.OpportunityHistories[opp.OpportunityHistories.size()-1].CreatedDate;
        DateTime previousStageChangeDate = opp.OpportunityHistories[0].CreatedDate;
        List<OpportunityHistory> ListOfOpportunityHistories = opp.OpportunityHistories;
        for(OpportunityHistory opphis :ListOfOpportunityHistories){ 
            if(lastOppStage != opphis.StageName && opp.Last_Stage_Update__c == null){
                lastUpdate = previousStageChangeDate;
                break;
            }
            previousStageChangeDate = opphis.CreatedDate; 
        }
        opp.Last_Stage_Update__c = lastUpdate.Date();
    }

    update listOfOldOpp;
}

    global void finish(Database.BatchableContext BC) {

    }
}

Answer 1

Perhaps the quick fix is to add a break statement to drop out of the OpportunityHistory loop as soon as the required value is found (right after opp.Last_Stage_Update__c = opphis.CreatedDate.Date();) rather than to keep looping and so keep consuming CPU seconds.

But assuming the problem is that it is a large number of OpportunityHistory objects being iterated over, cutting down the number of those down in the original query would also make sense. I'm not exactly sure which date you are trying to set on the Opportunity so this is only an example of the sort of change you could make:

Select Last_Stage_Update__c, (
        Select CreatedDate, StageName
        From OpportunityHistories
        where StageName  != ''
        order by CreatedDate desc
        limit 1
        )
From Opportunity
where Last_Stage_Update__c = null
and IsClosed = false

Answer 2

It could be an issue with the query in start method which is fetching child records with their parent records. As both are in million it could be possible that it is taking time in preparation phase itself and hitting the CPU time limit. So separating the query on child records and doing this in the execute() could be helpful.