Batch Apex to calculate rates based on more than 50k records

Question

I would like to calculate rates based on sets of Opportunities returned from a query -- this query regularly returns way more than the 50k limit allowed. At a high level, I know that I can get around that limit by moving this logic into a Batch context. The problem is, I'm not sure how to do it given what my query logic is. I basically have to query all Opportunities, i.e. "FROM Opportunity", but I'm also performing a subquery to grab all OpportunityLineItems underneath each opp.

I've been looking for examples online but I'm not finding any. Does anyone know what the nuances are behind querying more than 50k records for a query that has a subquery in it -- and how this works in a Batch context?

Again, I need to have all the opp records and their child records, and then calculate a list of rates based on that full list. So I can't calculate the rates in each individual batch per se.

I appreciate any help. If I should provide more detail, let me know. Thank you.

Answer 1

The QueryLocator that a Batchable class uses is subject to a 50,000,000 row limit, not just 50,000 that you get in most other contexts. Do keep in mind, though, you've only got 12,000,000 bytes of heap, so you will need to do your aggregation in each execute method; you can't store all the records in memory to the end. Also, as a performance enhancement, don't query the children until you get to the execute method; this will drastically decrease the start time of the batchable.

public class MyBatch implements Database.Batchable<sObject>, Database.Stateful {
  public Database.QueryLocator start(Database.BatchableContext context) {
    return Database.getQueryLocator([SELECT Id FROM Opportunity]);
  }
  public void execute(Database.BatchableContext context, Opportunity[] scope) {
    Opportunity[] opps = [SELECT (SELECT Id FROM OpportunityLineItems) FROM Opportunity WHERE Id = :scope];
    // Do stuff here
  }
  public void finish(Database.BatchableContext context) {
    // Finalize data here
  }
}

Answer 2

I ended up getting around the query row limit issue by creating Rollup fields on the Opportunity to summarize OpportunityLineItems. Even though the lookup to Opportunity on OpportunityLineItem is not labeled a Master-Detail relationship in Salesforce, you are in fact able to create Rollup fields to summarize it, which is what got me past my issue.