Our org is hitting the 101 SOQL limit during bulk uploads. We use a Trigger Handler pattern, but with multiple managed packages installed, the total query count is exploding. I've bulkified my own code, but the cumulative limit is still a problem. Does anyone have advice on using "Static Sets" to prevent redundant queries or implementing a "Service Layer" to manage data access across the entire transaction?
3 answers
To combat the 101 limit in complex environments, you need a "Singleton" pattern for your data access. By creating a static Map or Set within a service class, you can cache query results for the duration of the transaction. Before running any SOQL, your code should check if the data already exists in the static variable. Additionally, check your "Order of Execution." If you have multiple workflows or flows triggering the same Apex multiple times, your query count will double or triple. Consolidating logic into a single Trigger per object is the industry standard for keeping the query footprint small.
Have you analyzed the "Query Plan Tool" in the Developer Console? Sometimes the issue isn't the number of queries, but that the queries are non-selective, causing the database to work harder and stay open longer. Could adding indexed filters to your SOQL help reduce the overall transaction time and potential for hitting limits during heavy processing?
Also, consider moving non-critical logic to @future or Queueable Apex. It resets the governor limits for that specific execution, giving you a fresh 100 SOQL queries to work with.
Exactly, Susan. Queueable is especially powerful because you can chain jobs and even pass complex objects, which makes it much more flexible than @future for asynchronous heavy lifting.
Michael, the Query Plan Tool showed that my queries were selective, but the sheer frequency was the killer. I implemented Patricia's Singleton suggestion by caching the RecordType IDs and Profile data in a static utility class. This alone reduced my total SOQL count by 30 queries per transaction! It’s amazing how much overhead those repetitive "lookups" add when you’re dealing with 200-record batches.