Chunking in Apex trigger for large data sets (without hitting governor limits)

Question

I have a after update trigger which needs 4 SOQL queries. I also have an interface where we load 10k records for this object.

There can be a scenario where user tries to update all 10k records. The trigger will in turn divide it in chunks of 200. The problem is that governor limits are not reset between these chunks.

So ideally I need a limit of 200 SOQL queries for my trigger execution to pass.

I get stopped at 100 SOQL limits.

I cannot use batch as I need a front end synchronous update for the records. Any other option that I can use to make sure that processing of 10k records are successful?

Thank you

Answer 1

Fundamentally you cannot do what you want without doing one or more of the following:

1. Reducing the maximum bulk size that your "interface" uses.
2. Reducing the number of SOQL queries that you perform synchronously.

In reducing the maximum bulk size, you can leave the user with a "synchronous" experience by having the "interface" actually do the chunking itself. This is a good idea because there are other limits you could hit (such as maximum number of SOQL query rows) in a given transaction. Your "interface" can perform each chunk in an independent transaction, thereby avoiding such problems. This works when the "interface" is a custom Salesforce or web page (and you can write the chunking orchestration in JavaScript), or some external system.

For an external system, you can have that system split the bulk transaction into pieces, or if that's not practical consider introducing some form of middleware that does this.

To reduce the SOQL queries, consider whether you could combine (at least some of) these into a single query and consider whether aggregate queries can help you avoid SOQL query row limits. If the additional data you query is typically shared between all the various records across the transaction, consider using a transaction-scoped cache of results that can be shared between trigger invocations in that transaction.

Last option I can think of is to make this processing async but ensure that each record is individually marked as having (or not) been processed and then updating the "interface" to poll data until it is all processed (or leverage a push type notification from Salesforce). Clearly this may or may not be possible depending on what the "interface" is and how much control you have over its implementation.

You must always implement your solution taking Salesforce limits into consideration, otherwise you will fall foul of issues like this.