Batch Apex - How to Handle Many SOQL Queries

Question

I have a use case where I need to update the "Type" field on account records, based on the state of records that are related to the account. Specifically I'd need to query Opportunities, Contacts, Invoices, and custom Usage object records in order to classify the "Type" of an account.

I can easily do a one-time data fix, but I'm looking for a best practice on how I can keep this information updated moving forward. I was thinking batch Apex to query all records, process my logic, and update the accounts, but I'm concerned I'll run into SOQL limits.

Can someone please respond with the best way to architect something like this? I can see in the future I'd need to add other objects to my SOQL queries as well.

Thanks for the advice.

Answer 1

This is a broad area and the right solution is going to be somewhat specific to your data and Type model and highly dependent on your data volume in Account and the child objects thereof.

I see two clear ways forward here, which aren't mutually exclusive.

The first thing I would do is to aggressively use rollup information (whether done with native Roll-up Summary Fields, DLRS, or custom triggers) to aggregate or "cache" the detail that goes into determining the Account's Type into fields on the Account in real time. Depending on your criteria, if that approach were broadly applicable, you might be then able to calculate the Type in real time using a trigger or even Process Builder. You'd just have one or more fields per child object on the Account to roll up a specific criterion, and use those fields to make the determination.

The other, if the above is not enough to cover all of your criteria or is deemed unworkable, would be to perform one or more child queries within the execute() method of the batch process for each individual batch of Accounts over which the batch is executing.

Ideally, I would try to reduce the child object queries to something like SELECT count() FROM Child_Object__c WHERE Account_Id__c IN :scope AND MY_CRITERIA_HERE GROUP BY Account_Id__c, such that your decision can be made by the count of matching children.

You could, of course, do child subqueries in an overall Account query if the data volume is conducive to such an approach, and then iterate over the child objects to do whatever evaluation is required in Apex.

The expected data volume of the child objects is important to know here, as is the number of fields you need to retrieve to determine the Type. It's maybe unlikely, but not inconceivable, that with a large batch size, high volume child objects, or parent-child data skew that you could hit the heap limit, although in asynchronous code it's pretty high (12MB).

If you're able to reduce some of your criteria to roll-up type data, that will dramatically reduce the amount of work your batch class has to do, even if it is still running one or more child object queries.