1

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.

Improve this question
1
  • 1
    Database.Stateful and subquery not an issue in batch context
    – cropredy
    Feb 17, 2021 at 16:16

2 Answers 2

Reset to default
3

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
  }
}
Improve this answer
17
  • Hey Thank you for this response! This really helps me. If you have time, could you also show me how this would attach to a Scheduable class? I am looking everywhere online and I can't find a straight answer
    – Cory_16
    Mar 10, 2021 at 17:19
  • Nevermind, I think I got it now. Thanks so much!
    – Cory_16
    Mar 10, 2021 at 17:31
  • Actually if you do have time, how would you setup a Schedulable class to call this Batch class? Are there any nuances associated with that? (Like what to set the batch size for example, and does that impact the design/order of calculating the rates?) Thank you! @sfdcfox
    – Cory_16
    Mar 10, 2021 at 17:37
  • @Cory_16 You can build the schedulable directly into the class: public class MyBatch implements Database.Batchable<sObject>, Database.Stateful, Schedulable { ... public void execute(SchedulableContext ctx) { Database.executeBatch(this,2000); } As long as you're not trying to pull more than 150k child records per execute call, you can set the batch size as large as you want. I'd probably intentionally set it to 2000 (as demonstrated above), since it's just simple calculations. Larger batches generally perform better than small ones, unless you hit governor limits.
    – sfdcfox
    Mar 10, 2021 at 17:46
  • Thank you that makes sense. Regarding child records; I know you said that Database.getQueryLocator has the 50 million row limit instead of the regular 50k limit. With that in mind, when I query the child OpportunityLineItems in the execute method, is that then limited to 50k instead of 50 million? (Because at that point a regular query is being written; not Database.getQueryLocator). I ask because the OpportunityLineItems under an Opp are often like 10 to 1, ie each opp might have ten line items. Does it still get around the limit that way?
    – Cory_16
    Mar 10, 2021 at 17:48
0

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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .