1

I scheduled an apex job to run daily at midnight but noticed that the job failed due to the Apex CPU time limit exceeded error. The code works in my developer sandbox but not in production. I'm wondering if it is because of something in my SOQL query, but unclear on where the culprit would be. The query only returns about 500 records in production. Any help is appreciated- here is my class:

global without sharing class UnassignAccountsDueToInactivity implements Schedulable {
  global void execute(SchedulableContext ctx) {
    List<Account> acctList = [SELECT Id, Name, OwnerId 
                              FROM Account 
                              WHERE (OwnerProfileName__c LIKE '%AE%' OR OwnerProfileName__c LIKE '%SDR%') AND 
                              (Current_Customer__c != 'Current Customer A' AND Current_Customer__c != 'Current Customer B' AND Current_Customer__c != 'Current Customer C') AND
                              Number_of_Open_Opportunities__c <= 0 AND
                              DaysSinceLastActivity__c >= 30 AND
                              DaysSinceLastOwnerChangeDate__c >= 7];

    if(!acctList.isEmpty()){
      for(Account acc : acctList){
        acc.DisqualifiedReason__c = 'No response';
        acc.OwnerId = '000000000000000';
      }
      update acctList;
    }
  }
}
  • Does Production contain any additional automation that runs on update of Accounts? Have you wrote a unit test for what you are trying to do? Is it passing in both orgs? Have you looked at the debug logs? Anyway, the quickest way to debug this is by pasting the execute code in Dev Console in Prod and throwing an exception in the end (to force a rollback). Look at the debug logs and see what's happening after the update call. – Mossi Apr 2 at 22:22
  • The query is so much un-selctive. It has not like and != filters which for sure will break in prod with large data volume. Follow this guide to make your SOQL selective. help.salesforce.com/… – Pranay Jaiswal Apr 2 at 22:33
  • 1
    @PranayJaiswal A non-selective query doesn't fail with a CPU timeout error. It throws a QueryException. While efficiency could be enhanced here, even the most inefficient of queries wouldn't take 60 seconds to run (the async CPU time limit). The root of the problem must be somewhere else. – Mossi Apr 2 at 22:45
  • Yea @Mossi you are right, its part of database time and not cpu time. – Pranay Jaiswal Apr 2 at 22:47
  • Can you download the debug log and analyze where it's taking more time? Use - apextimeline.herokuapp.com . Then post that. – Arabinda Apr 3 at 9:34
1

I recommend you use Data Loader for this task.

  1. Export the query you have already written
  2. Manipulate the CSV to set DisqualifiedReason__c and OwnerId to the proper values.
  3. Import the manipulated CSV

You don't need any Apex if you approach the task this way. You can also break the query out into a few discrete chunks to simplify the SOQL.

If you still want this logic to run daily and expect large volumes, you should use a batch to chunk the records.

public with sharing class MyBatch implements Database.Batchable<SObject>, Schedulable
{
    public void execute(SchedulableContext context) { Database.executeBatch(this); }
    public Database.QueryLocator start(Datbase.BatchableContext context)
    {
        return Database.getQueryLocator([/*current query*/]);
    }
    public void execute(Database.BatchableContext context, List<Account> records)
    {
        for(Account record: records){
            record.DisqualifiedReason__c = 'No response';
            record.OwnerId = '000000000000000';
        }
        update records;
    }
    public void finish(Database.BatchableContext context)
    {
        // optional implementation
    }
}

If you are still having trouble with timeouts, you can reduce the batch size in your execute(SchedulableContext) method. You could go as low as one record per chunk, though this size would cause your batch to run quite slowly.

Database.executeBatch(this, 1); // default is 200
  • Thanks for the reply, Adrian. Ideally, I would have this scheduled to run automatically every day without having to worry about doing it manually through data loader. – Patrick Marks Apr 2 at 23:40
  • Thanks again for the response, Adrian. I'm getting an error on Line 1 that says my class must implement the method: System.Iterable<SObject> Database.Batchable<SObject>.start(Database.BatchableContext)". Also getting an error on line 4 that says Database.getQueryLocator is an invalid type. Any thoughts? – Patrick Marks Apr 3 at 19:32
  • Just needed to fix the method signature. – Adrian Larson Apr 3 at 20:26
  • Thanks for the help-I'm still getting the same error on Line 1 (must implement the method: System.Iterable<SObject> Database.Batchable<SObject>.start(Database.BatchableContext)) – Patrick Marks Apr 4 at 20:21
0

I'd go with running restructuring your code into a batchable where you start the batchable from the execute method of the schedulable. (This is Adrian's second suggestion clarified a bit.) This way you can kick off the batchable at an appropriate time, and break the work down into chunks that stay within the governor limits.

0

Note that when you do the update statement (of 500 Accounts), the transaction scope is all 500 records even though any trigger/PB/WF on Account will be chunked in groups of 200,200 and 100 Accounts. It is these downstream functions that are most likely burning through your CPU.

Depending on your business, you could consider dynamically disabling the triggers and process builders/WFs with some gate (switch) as a straight up DML of 500 Accounts should comfortably run in the available CPU time. Such a gate might be based on a known running user (a dedicated user that schedules the job).

All that said, to future proof all of this, I agree 100% with Adrian's batchable suggestion.

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.