Nested SOQL Queries and Batch Apex

Question

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?

Answer 1

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 too:

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.

Answer 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.