0

I have a batch class with the below start method and its hitting error QUERY_TIMEOUT: Your query request was running for too long. The conditions added in the query are required. Is there a way to fix or optimize this query?

    public Iterable<SObject> start(Database.BatchableContext context) {
    List<Training__c> trainings = [
            SELECT Status__c,
                    Type__c,
                    Module_Type__c,
                    Scheduled_Date__c, future_Schedule_date__c,future_Schedule_date2__c

            FROM Training__c
            WHERE Type__c = 'N/A'
            AND Scheduled_Date__c = NULL
            AND is_voc__c = TRUE
            AND VTD1_Status__c IN ('Future Training')
            AND (future_Schedule_date = TODAY OR future_Schedule_date2 = TODAY)
    ];
    return trainings ;
}

2 Answers 2

1

I don't know how many of those fields are indexed, but there is another reason for your query to take that long: too many records to be returned by the database.

In this case you should look into using a query locator instead of trying to query all records at once. With a query locator you'll be able to query millions of records in smaller chunks.

From the Apex Developer Guide on "Using Batch Apex" documentation:

When you’re using a simple query (SELECT) to generate the scope of objects in the batch job, use the Database.QueryLocator object. If you use a QueryLocator object, the governor limit for the total number of records retrieved by SOQL queries is bypassed. For example, a batch Apex job for the Account object can return a QueryLocator for all account records (up to 50 million records) in an org.

Check the "Batch Apex Examples" section. The first example uses a query locator.

0

The query can be optimized using the SOQL query optimization mechanisms described e.g. in this Trailhead: https://trailhead.salesforce.com/content/learn/modules/database_basics_dotnet/writing_efficient_queries

Some first thoughts:

  1. replace AND VTD1_Status__c IN ('Future Training') by AND VTD1_Status__c = 'Future Training'
  2. if there are any text fields among the filters, make those text fields 'external ID' (as described here: https://help.salesforce.com/s/articleView?id=000320036&type=1)

Based on the amount of records in the table more advanced mechanisms could be needed, like https://blog.bessereau.eu/assets/pdfs/salesforce_large_data_volumes_bp.pdf

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.