0

We're currently running a monthly batch job to update a single field on every Account in our Org. We believe we were hitting governor limits so we have scaled back the batch size from the default of 200 to 50, which seems to have resolved our issues for. However, we're wondering what the best practices might be to improve our SOQL queries to mitigate against further issues.

(Apex and SOQL syntax and field names are shortened for clarity)

Our Account object has the following field:

  • Annual Change

We have an Account -> Billings relationship. The Billings object has the field Invoice Subtotal.

The Annual Change is computed every month by summing up the invoice subtotals from the last 12 months and comparing that to the subtotals from the previous 12 months. This is a sliding 12 month window.

The pseudo code below highlights the nested for-loop:

public Database.QueryLocator start(Database.BatchableContext context) {
  String query = 'SELECT Id FROM Account';
  return Database.getQueryLocator(query);
}

public void execute(Database.BatchableContext context, List<Account> accounts) {
  // Pseudo code:
  for (Account account : accounts) {
    Decimal value = 0.0;
    Decimal lowerValue = 0.0;
    Decimal upperValue = 0.0;

    // startDate is 24 months ago. 
    // midDate is 12 months ago. 
    // endDate is 0 months ago.

    for (Billing billing : [
      SELECT Id, subtotal, date
      FROM Billing
      WHERE
        Account.Id = :account.Id
        AND date >= :startDate
        AND date <= :endDate
    ]) {
      if (billing.date <= midDate) { 
        lowerValue += billing.subtotal;
      else {
        upperValue += billing.subtotal;
      }
    }

    // Error handling omitted. 
    account.value = lowerValue / upperValue
  }

  update accounts;
}

public void finish(Database.BatchableContext context) {
  // Not used.
}

Are the better ways to go about this sort of double iteration that we can use? Some of the solutions we've considered include:

Option 1

Select all the Billings in start(), grouped by Account.id and then process those. But how are results processed in batches when group by was used? The final value that is written back to the Account is dependent on all of its Billings.

Option 2

Similar to Option 1, but instead of using group by, we use a stateful batch job and do all of our own grouping. However, the burden then falls on the finish() method to perform a batch update() on over 10,000 Account records, which will surely fail in its own right.

Option 3

In execute, we can aggregate all of the Account.Ids into a Set<> and then do a single SOQL query for Billings with an Account.Id belonging to that Set. This adds a bit more complication to the code as we'd then need a Map<> to keep tracking of our running totals. We're also worried that this query might hit its own limits.

Option 4

Just leave it as-is, ignore the nested for-loop warnings and make sure we define a small enough batch size in our scheduler to not run into problems.

Edit 1

Option 5

Fetch all of the Billings in start(), but ORDER them by their associated Account.Id. Using a stateful batch job, we can keep track of the "current Account.Id" and the "current running value".

In execute(), we can iterate through the Billings and if the Account.Id changes, the we can just call update with what we have computed because we know there won't be any more Billings with that Account.Id. From there, we just reset the running state and finish with one last call to update in finish to catch the last pass.

Are we overlooking other possible solutions? Thanks in advance.

3
  • Depending on the math you apply to subtotal, you might be able to use an aggregate query - e.g. if you purely SUM them - and you could cover all the accounts (with appropriate GROUP BY) so you have exactly one query that does everything and all you are left with is updating the accounts in a single loop and one DML.
    – Phil W
    Commented Jun 12, 2023 at 6:27
  • Wondering if you can keep the batch running on the accounts, and then for each batch perform an aggregate query on the data you wish to sum/average on. In theory you would need to issue maybe two queries (for the two different time frames). Did you consider that? Is that a valid option? Commented Jun 12, 2023 at 21:32
  • @PhilW @Renato I made the pseudo code above a bit more clear as to what the calculation is we're doing. We don't have too much experience with aggregate queries so that's something we'll definitely look into. All we're really doing is looking at Billing.subtotal over the last 24 months and seeing if the most recent 12 months is better than the trailing 12 months. This 24 month window slides forward by one month, every month.
    – kennyc
    Commented Jun 12, 2023 at 23:40

2 Answers 2

0

If you use your Option 1, at least you would do only 1 query instead of 200 or 50 or whatever your batch size is. The nested for loop might not matter, depending on the length of time of processing. From your description it does not sound like it's too heavy. You can then use a map to organize them for processing, maybe something like Map<Id,List<Billings>> Maybe refer to this for how to build it. Then iterate through the map one account Id at a time, put the answers into your account records, add them to a List<Account>, and update. I realize this answer is like your Option 3. Even though it adds complexity with the map, it is more efficient than making a query per account. Salesforce really tries to discourage making a lot of queries where it's possible to make just one. So if you are running into governor limits, that is a likely first one to hit.

A totally different approach, depending on what your calculations are, you might be able to get away with using formulas and/or rollup fields instead of Apex. Maybe use formulas in the Billings records and a rollup field in the Account. Just an idea. Hard to be specific without seeing what you're calculating.

7
  • Our calculation are very light. We compute two values: the sum of a field on Billings for the last 12 months, and the same sum on the same field for the previous 12 months. The final output is the percentage change between the two fields. ("Annual Change in Billing Subtotals - Year over Year") Any option that tracks state within execute() then needs to call update in finish() passing in all the Accounts. That's our concern. Can we update 10k records inside finish() by making a single update call without hitting limits?
    – kennyc
    Commented Jun 12, 2023 at 15:39
  • I'm not entirely sure I follow your latest suggestion. We need the SUM of Billing.subtotal for a given Account. In a perfect world, a formula or roll-up field on Account could work in conjunction with your idea, but I don't see how we can define a roll-up field that computes the SUM based on a secondary predicate like "Is 12 Months". (Nor do I even see our Billings object as an option for the Summarized Object field, perhaps because it's part of a third-party package.
    – kennyc
    Commented Jun 13, 2023 at 2:01
  • Ah, well some third party packages allow you to make custom fields. In theory, you can create a text formula field that identifies each billing record as being "last 12" or "previous 12", then you would have to make a text field, and a simple record-triggered flow that copies the value of that field to the text field, because roll-up summaries can't filter on formulas. Then you can create 2 rollup summary fields of type SUM on the Account, which sum the values from the child Billings, with filtering on the text field. Sounds crazier the more I think of it. Just keep it in your back pocket.
    – Matt D
    Commented Jun 13, 2023 at 2:18
  • I was able to get the non-apex idea configured in a sandbox. It's a good proof of concept, but yeah, if the package does not allow custom fields to be added to their objects, it's a moot point.
    – Matt D
    Commented Jun 13, 2023 at 3:21
  • As to updating 10k records, I think you are still going to have to do it in batches. My first suggestion was for the purpose of removing SOQL from your loops, which is highly discouraged. Do you have an indication of what limit was being hit?
    – Matt D
    Commented Jun 13, 2023 at 3:23
0

You can query all the billings in the start method and iterate on that in the execute. As there will be multiple billings for an account, you need to make the batch stateful. Create a global variable in the batch which will hold all the accounts and their billings.

Map<Id,List<Billings>> accountAndItsBillings = new Map<Id,List<Billing>>();

//execute->
List<Billings> accountsBillings;

for(Billing bills : listOfBills){
  
      //you can add a condition here to handle whether you want to update the account or not
      if(accountAndItsBillings.containsKey(toAdd.Id)){
         accountsBillings = accountAndItsBillings.get(toAdd.Id);
         accountsBillings.add(bills); 
        accountAndItsBillings.put(bills.Account.Id,accountsBillings);
      }else{
         accountsBillings = new List<Billings>();
         accountsBillings.add(bills);
         accountAndItsBillings.put(bills.Account.Id,accountsBillings);
      }
 
}

Now you can pass this accountAndItsBillings to another batch in the finish method. In another batch, you can query all the accounts using the Map.

//global variable to hold all the accounts to update
List<Account> toUpdate = new List<Account>();
Decimal value=0.0;
//execute
for(Id accountId : accountAndItsBillings.keyset()){
 for(Billings bills : accountAndItsBillings.get(accountId)){
    if(bills.date >= startDate && date <= endDate){
       if (billing.date <= midDate) { 
        value += billing.subtotal;
      else {
        value += billing.subtotal;
      }
      toUpdate.add(new Account(Id=accountId,value=value));
    }
 }
}
if(toUpdate.size()>0){
  update toUpdate;
}
2
  • Our concern with this strategy is that Billings for a single Account will be split across batches. The only way we'd know that all Billings for a given Account have been processed would be when finish() is called, but then we'd need to flush an update to all of our Account records within finish(), which I'm not sure is a good idea.
    – kennyc
    Commented Jun 12, 2023 at 15:35
  • @kennyc, please check the updated answer. Commented Jun 13, 2023 at 5:18

You must log in to answer this question.

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