I am facing a sudden bug that arose from a custom scheduled batch class in the system. This scheduled job has been running smoothly for over a year now, but it seems as though a recent influx of records (~2,000) for which the batch class iterates over has thrown the process over the CPU time limit.
Every time the job runs, the ending "Status" (Setup > Monitor > Jobs > Apex Jobs) is the same as it always has been - "Completed". However, in the "Status Detail" section, there is now a "First error: Apex CPU time limit" message that appears after every run.
After researching potential solutions, I have tried modifying the batch class to avoid this timeout by:
- reducing the batch size (down to as low as 10)
- modifying the initial query (Start method) - removed sub-fields, etc.
- breaking apart/moving around the data processing operations within the Execute method
Nothing has seemed to help at all. When looking at similar posts around the topic, it seems as though the problem lies in the initial query. I've used the Query Plan tool (developer console) to try and optimize/debug the initial query, and it tells me that the two filtering fields are "Not considering filter for optimization because unindexed". Both fields are custom picklist fields.
Am I going down the right path with this? If Salesforce support could make both fields "custom index" fields, would this likely solve the problem / speed things up (i.e. not timeout)? Are there other things/places I should be looking at?
Any help would be greatly appreciated...
Here is the query from the Start method:
return Database.getQueryLocator( 'SELECT Id, Lookup_1__c, Lookup_2__c, Lookup_1__r.Name, Lookup_2__r.Name, External_Id_Field__c, '+ 'Field_A__c, Name, Lookup_3__c, CreatedDate '+ 'FROM Custom_Object__c '+ 'WHERE Custom_Picklist_Field_1__c = \'Value A\' '+ 'AND Custom_Picklist_Field_2__c = \'Value B\'');