3

I am writing an batch Apex script and the query in the Start method will have a nested query like [Select id, name, (select id, name from Opps__r) from Account]

I know that batch apex will try to break up these records into batches of 200, or whatever batch size I specify, however, some Accounts may have over 200 Opps, maybe even over ten thousands. In this case, how will the records be broken up? Will it just generate an error or will it break up the records into reasonable batches somehow?

0

Your present query would result in the parent (Account) objects being broken up into batches but not the child objects.

So, as I think you already suspect, best to restructure your query to be on the child object so the number of records is determinate; you can pull in the parent fields to:

select Id, Name, Account__r.Id, Account__r.Name from Opportunity

You will have to refactor the execute method to match.

PS

See comment about handling Account objects that don't have Opportunities.

  • 1
    Hi Keith, I suspected this, but the issue with this approach is I may not get all of the records for Account. In the approach you mentioned, how would I ensure that I got Accounts that had no Opportunities? – Ibad Rahman Jan 6 '17 at 13:52
  • @IbadRahman Good point - you wouldn't. You would need a second batchable for that. All depends on your overall needs: having to write 2 batchables isn't such a big deal if it ensures you don't hit governor limits. – Keith C Jan 6 '17 at 14:16
  • I think I may go with your approach, and hack a way to iterate through the parent object (of which there are relatively few) by querying then in each execute method (or querying them once in the start method into a persistent list, using the Database.Stateful interface) and then iterating through this list in each execute() call, and iterate the rows from start() in a nested loop. I'll also keep a set of parent objects already processed, so that when I am processing a row where the parent object was already processed I only update the child object. – Ibad Rahman Jan 6 '17 at 14:18
  • Yes, having two batch scripts is also an option... one that I was dreading a bit. That might be the correct option, if one cannot assume that there are only a few parent objects. – Ibad Rahman Jan 6 '17 at 14:19
2

You shouldn't use child sub-queries in the start method, as it can have undesirable side effects, including failure to start in time, or errors while processing if the child lists include a QueryLocator. In fact, to make sure you don't have stale data, I usually recommend re-querying the data once you get into the execute method anyways.

Here's how I would rewrite your batch process.

public Database.QueryLocator start(Database.BatchableContext context) {
  return Database.getQueryLocator([SELECT Id FROM Account]);
}
public void execute(Database.BatchableContext context, Account[] scope) {
  Account[] records = [SELECT Name,(SELECT Name FROM Opps__r) FROM Account WHERE Id = :scope];
  // Do something with records
}

This way, you know that you haven't missed any accounts, and can still access all of the opportunities.

Note: If you're trying to handle more than 10,000 opportunities (and you'll need to update potentially all of them), then you're going to have to get more creative by way of a Queueable call. You are allowed one Queueable call per execute method, so you can still handle a larger number of opportunities, but it won't be as easy.

  • The problem with that is that is I have something like 12000 Opps for an account, and I need to update them all, I run into the 10,000 records limit on the number of records I can process through DML in a single run-context. – Ibad Rahman Jan 6 '17 at 14:13
  • @IbadRahman You can put the updates into a queueable and run them asynchronously if need be. – sfdcfox Jan 6 '17 at 14:17
  • 1
    @IbadRahman Alternatively, if that's not good enough, then you're probably going to have to use two batches, as suggested by KeithC instead. You can actually query for all accounts without opportunities using SELECT Id FROM Account WHERE Id NOT IN (SELECT AccountId FROM Opportunity) – sfdcfox Jan 6 '17 at 14:19

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.