0

I have created a Batch which is supposed to pull Completed tasks and Reopen them. It's a simple implementation (code attached), but what i observe is the Query is taking too long and ultimately aborts the batch.

I am not able to figure out what's causing this query to fail/take too long. Any help is appreciated!

public class ReopenReferralTasksBatch implements Database.Batchable<sObject>,Database.Stateful {

    public Database.QueryLocator start(Database.BatchableContext BC){
        String TASK_QUERY = 'SELECT Status FROM Task WHERE Status = \'Completed\' AND Type = \'Referral Enquiry\'';
        TASK_QUERY += ' AND Referral_Reopen_Date__c = TODAY';
        return Database.getQueryLocator(TASK_QUERY);
    }
    public void execute(Database.BatchableContext BC, List<Task> tasksToBeReOpened){
        List<Task> reOpenedTasks = new List<Task>();
        for(Task task : tasksToBeReOpened){
            task.Status = 'Open';
            reOpenedTasks.add(task);
        }
        
        if(reOpenedTasks.size()>0){
            Database.SaveResult[] saveResultList = database.update(reOpenedTasks);
            Integer index = 0;
            for(Database.SaveResult svrslt : saveResultList){
                if(!svrslt.isSuccess()){
                    String errMsg = svrslt.getErrors()[0].getMessage();
                    errorMap.put(reOpenedTasks[index].Id, errMsg);
                }
                else{
                    String sucMsg = baseURL + '/' + reOpenedTasks[index].Id;
                    successMap.put(reOpenedTasks[index].Id, sucMsg);
                    IdToSObjectMap.put(reOpenedTasks[index].Id, reOpenedTasks[index]);
                }
                index++;
            }
        } 
    }
    public void finish(Database.BatchableContext BC){
        WHJ_BatchMailHandler.Sendmail(IdToSObjectMap, errorMap, successMap, 'ReopenReferralTasksBatch');
    }
}

Not just that, if a run the following query in dev console, it times out:

SELECT Status,Referral_Reopen_Date__c FROM Task WHERE Status = 'Completed'

Edit: Batch Size: executing with no explicit batch size from anonymous window.

Start method is the one where this limit is exceeded.

Referral_Reopen_Date__c : is a date type field.

If i add a today clause (Referral Reopen Date = Today) in the following:

SELECT Status,Referral_Reopen_Date__c FROM Task WHERE Status = 'Completed'

It still times out on the QUERY stating: It's taking too long!

6
  • Can you apply a condition to reduce the number of tasks that this query is matching? E.g. make sure you only look at those updated in the last day, week or month? EDIT: Ah, sorry, I see you restrict to TODAY in the code, but not in what you post to the Dev Console query editor. What happens if you add the TODAY clause there as well?
    – Phil W
    Jul 5 '21 at 11:03
  • What batch size are you using? What if you reduce that? Which method causes the time exceeded message? Is the referral reopen date a Date or Datetime field, or is it a formula? Please edit your question to add more detail.
    – Phil W
    Jul 5 '21 at 11:07
  • If the timeout is coming on querylocator then you can limit it to some 20 millions (or any number based on your testing) and then chain the batch in finish method again to run for another 20 million. Fire the email only when there is nothing to process. Jul 5 '21 at 11:12
  • @PhilW: I just appended a clause: 'AND LastModifiedDate = LAST_N_DAYS:30' to my query and it seems not failing now. I am not really an expert on SOQL optimisation but i think this brought down the size of the chunk through which the query traverses. Would you care to comment why exactly this worked ? Jul 5 '21 at 11:22
  • There is a top timeout cap for all SOQL query (2 minutes timeout). And I believe that is getting crossed when your SOQL is trying to query all tasks from database. Adding LAST_N_DAYS:30 creates a filter cap on your data to fetch only last 30 days of tasks, so your query result is returned under those 2 minutes. Jul 5 '21 at 11:27
1

I just appended a clause: 'AND LastModifiedDate = LAST_N_DAYS:30' to my query and it seems not failing now. I am not really an expert on SOQL optimisation but i think this brought down the size of the chunk through which the query traverses.

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.