Beyond SOQL101: Mastering the Stateful Selector Pattern in Apex

In high-scale Salesforce environments, resource conservation is the ultimate design goal. Without a dedicated data strategy, redundant queries within a single transaction don’t just waste CPU time. They also risk hitting the hard wall of Governor Limits.

The Problem: Transactional Redundancy

In complex transactions, the same record is often requested by multiple independent components:

• Triggers checking record status.
• Service Classes calculating SLA details.
• Validation Handlers verifying ownership.

Without a strategy, each call initiates a fresh database round-trip. This “fragmented querying” leads to System.LimitException: Too many SOQL queries: 101.

The Solution: The Stateful Selector Pattern

By centralizing data access and implementing Memoization (Static Caching), we ensure that once a record is fetched, it resides in memory for the duration of the execution context.

The Core Implementation Steps:

1. Encapsulate: Use inherited sharing to ensure the selector respects the caller’s security context.
2. Define a Transaction Cache: Use a private static Map<Id, SObject> as an in-memory buffer.
3. Apply “Delta” Logic: Identify only the IDs missing from the cache before querying.
4. Enforce Security: Always use WITH USER_MODE for native FLS and CRUD enforcement.
5. Serve & Hydrate: Bulk-fetch missing records, update the cache, and return the result set.

The Pattern in Practice

Below is a refined implementation of a Stateful Account Selector:

/**
 * @description Account Selector with Transactional Caching 
 * @author John Dove
 */
public inherited sharing class AccountSelector {
    
    // Internal cache to store records retrieved during the transaction
    private static Map<Id, Account> accountCache = new Map<Id, Account>();

    /**
     * @description Returns a Map of Accounts for the provided IDs.
     * Only queries the database for IDs not already present in the cache.
     */
    public static Map<Id, Account> getAccountsById(Set<Id> accountIds) {
        if (accountIds == null || accountIds.isEmpty()) {
            return new Map<Id, Account>();
        }

        // 1. Identify IDs not yet cached
        Set<Id> idsToQuery = new Set<Id>();
        for (Id accId : accountIds) {
            if (!accountCache.containsKey(accId)) {
                idsToQuery.add(accId);
            }
        }

        // 2. Perform bulkified, secured query for the "Delta"
        if (!idsToQuery.isEmpty()) {
            List<Account> queriedRecords = [
                SELECT Id, Name, Industry, AnnualRevenue, (SELECT Id FROM Contacts)
                FROM Account
                WHERE Id IN :idsToQuery
                WITH USER_MODE
            ];
            
            // 3. Hydrate the cache
            accountCache.putAll(queriedRecords);
        }

        // 4. Extract and return the requested subset from the cache
        Map<Id, Account> results = new Map<Id, Account>();
        for (Id accId : accountIds) {
            if (accountCache.containsKey(accId)) {
                results.put(accId, accountCache.get(accId));
            }
        }
        return results;
    }

    /**
     * @description Invalidation method to be called after DML 
     * to ensure the cache doesn't serve stale data.
     */
    public static void invalidateCache(Set<Id> idsToRemove) {
        accountCache.keySet().removeAll(idsToRemove);
    }
}

Why This Scales

• Reduced DB Contention: Minimizing SOQL round-trips frees up database resources for concurrent requests.
• Idempotency: You can call the selector 50 times in a recursive trigger flow, and it will only hit the database once.
• Clean Maintenance: Global filters (like IsActive = true) are updated in one method, not across dozens of classes.

Trade-offs: Advantages & Disadvantages

Feature	Advantage	Disadvantage
Governor Limits	Drastically reduces SOQL query count.	Can lead to Heap Limit exceptions if caching thousands of large records.
Performance	Sub-millisecond retrieval for cached records.	Increased complexity in handling cache invalidation after DML.
Maintenance	Single source of truth for query logic/security.	Risk of “Stale Data” if the record is updated but the cache isn’t refreshed.

Conclusion

The Stateful Selector pattern is a fundamental building block for enterprise-grade Salesforce architecture. It transforms your data layer from a performance bottleneck into a high-speed, secure, and predictable asset.