Batch Apex: [REQUEST_RUNNING_TOO_LONG] - can I optimise my class?

Question

I've written a batch class that grabs order ID's using a selective query in a QueryLocator method. I then execute my batch by running a SOQL for loop to get and update relevant big object records that have a matching Id/value on one of the orders retrieved.

Is there a better (read: more efficient) way that I can retrieve these big object records to update? Should I be using a map instead?

Note that one of my index fields on the big object isn't queryable without first finding that big object record (as in, its not available on any other sObjects).

My start/execute methods are below. Any suggestions on increasing efficiency to get rid of this error would be appreciated.

global class UpdateArchivedOrderLinesBatch implements Database.Batchable<sObject> {

global Database.QueryLocator start(Database.BatchableContext bc) {
    return Database.getQueryLocator([
        SELECT Id FROM Sales_Order__c WHERE Actual_Order_Date__c < 2014-01-01T01:02:03z
    ]);
}

global void execute(Database.BatchableContext bc, List<Sales_Order__c> orderIds) {

    List<Order_Line__b> olToUpdate = new List<Order_Line__b>();

    Decimal cad2usd = 0.77018;
    Decimal chf2usd = 1.00812;
    Decimal dkk2usd = 0.154710;
    Decimal eur2usd = 1.154220;
    Decimal gbp2usd = 1.3233;
    Decimal sek2usd = 0.1119;

    for(Order_Line__b ol : [
        SELECT Order__c, Order_Line_ExtId__c, Account__c, Account_Country__c, Actual_Order_Date__c, Currency__c,
        Final_Price__c, Final_Price_EUR__c, Net_Price__c, Returned__c, Item_Revenue__c, Return_Amount__c, Return_Amount_USD__c, Return_Amount_EUR__c,
        Order__r.Copy_Actual_Order_Date__c, Order__r.Copy_Account__c, Order__r.Account_Country__c, Order__r.Currency__c
        FROM Order_Line__b
        WHERE Order__c IN :orderIds]) {

        Order_Line__b line = new Order_Line__b();

        // set indexes to ensure this only updates existing records
        line.Order__c = ol.Order__c;
        line.Order_Line_ExtId__c = ol.Order_Line_ExtId__c;

        // backfill new field values from the related Order
        line.Actual_Order_Date__c = ol.Order__r.Copy_Actual_Order_Date__c;
        line.Account__c = ol.Order__r.Copy_Account__c;
        line.Account_Country__c = ol.Order__r.Account_Country__c;
        if(ol.Return_Amount__c == null && ol.Returned__c == 'true' && ol.Final_Price__c != null) {
            line.Return_Amount__c = ol.Final_Price__c;
        }

        // add missing EUR return amounts to lines
        if(ol.Return_Amount_EUR__c == null && ol.Returned__c == 'true' && ol.Final_Price_EUR__c != null) {
            line.Return_Amount_EUR__c = ol.Final_Price_EUR__c;
        }

        // add USD values to CAD lines
        if(ol.Order__r.Currency__c == 'CAD' && ol.Net_Price__c != null && ol.Final_Price__c != null) {
            line.Final_Price_USD__c = ol.Final_Price__c * cad2usd;
            line.Net_Price_USD__c = ol.Net_Price__c * cad2usd;
            if(ol.Return_Amount_USD__c == null && ol.Returned__c == 'true') {
                line.Return_Amount_USD__c = ol.Final_Price__c * cad2usd;
            }
        }
        // add USD values to USD lines
        if(ol.Order__r.Currency__c == 'USD' && ol.Net_Price__c != null && ol.Final_Price__c != null) {
            line.Final_Price_USD__c = ol.Final_Price__c;
            line.Net_Price_USD__c = ol.Net_Price__c;
            if(ol.Return_Amount_USD__c == null && ol.Returned__c == 'true') {
                line.Return_Amount_USD__c = ol.Final_Price__c;
            }
        }

        olToUpdate.add(line);
    }

    Database.insertImmediate(olToUpdate);
}

Answer 1

Although my query in the Start method was using an index, it wasn't qualifying as Selective in the dev console's Query Plan tool. The cost returned was 1.3, where SF says anything above 1 is no longer considered selective (see: https://help.salesforce.com/articleView?id=000199003&type=1)

I've added the following to my start method/query:

    String query = 'SELECT Id FROM Sales_Order__c ' + Label.UpdateArchivedOrderLinesWhere;

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

...and I'm now using a custom label to adjust my WHERE clause directly from production using timeframes on an indexed field (e.g. WHERE Actual_Order_Date__c > 2014-01-01T01:02:02z AND Actual_Order_Date__c < 2014-06-01T01:02:02z). I'll do this in chunks using as big of a timeframe as I can, while still maintaining a selective query.

If you have other optimisation suggestions please feel free to post them, but this is now running successfully in prod against ±7mill big objects rows related to ±2mill various custom object rows.

bonus edit: I wanted to add that once this apex job has its number of batches determined (ie the Select method has finished running), you can update the custom label (which contains the WHERE clause) and start another batch to run in parallel. This is probably common knowledge to most, but it might help you if you need to update millions of records on a pressed schedule.