Large System: Workarounds to avoid 101 SOQL limit exception

Question

We have a very large system doing a complicated business process. On account creation we are creating many dozens of custom objects and on the latest release today we have hit the wall of 101 queries and (maybe!?) today we can optimize a bit more to get under the limit but soon we will not be able to further optimize the queries.

This is due to large functionality increase. Assume our entire team has done the work perfectly (putting no SOQL querys in a loop and everything is done via Hashmaps and List selects etc.), does anyone have a brilliant way to break up the stack or to reduce the total number of queries?

My first thought is to split it in two so we can do 50 and 70 for example? Since we are clearly like 120 currently.

Has anyone else hit this problem? Has Sales-force worked with you to move this limit for a large system?

One Hack I was thinking of was along the lines of changing something in the front-end so it can send a second data create for a round 2. I would not bother the user at all but maybe we can play with the page to break it up and cause a second process to trigger.

Ideas welcome!

Answer 1

If you really do need so many queries, and are going to continue to do so, have you considered splitting up the work using the Queueable interface to run jobs asynchronously on Account creation? Something like:

public class Queueable1 implements System.Queueable
{
    public List<Account> accounts {get;set;}
    public void execute(System.QueueableContext qc)
    {
        // do something
    }
}

public class Queueable2 implements System.Queueable
{
    public List<Account> accounts {get;set;}
    public void execute(System.QueueableContext qc)
    {
        // do something else
    }
}

trigger AccountTrigger on Account (after insert) 
{
    Queueable1 q1 = new Queueable1();
    q1.accounts = Trigger.new;
    System.enqueueJob(q1);

    Queueable2 q2 = new Queueable2();
    q2.accounts = Trigger.new;
    System.enqueueJob(q2);

}

That way, you can achieve greater limits within each of the Queueable jobs by isolating one from another. Obviously, careful design and implementation is needed to ensure that any failed jobs are re-processed accordingly or if there is likely to be any chaining issues as a result of Accounts already being created in Batch or Queueable already.

Answer 2

An alternative to Phil Hawthorn's suggestion is to split things up by the number of records rather than by splitting by the functions which run on those records.

The system I came up with was to serialise the objects I was going to insert into JSON, and store that data in another object to be executed later in a separate context. Due to the data structure of my system, I knew the key points where the processing required would blow up. So, I modifed these to using my own batching system. Where I had code which did this:

update attendancesToUpdate;

I turned it into:

// The number of Attendances can blow up into a massive amount of processing
// So, use DeferredDmlWorker to do 200 at-a-time
DeferredDmlWorker.batchedCommit('update', attendancesToUpdate, true);

Where that function is written as:

public static List<Database.SaveResult> batchedCommit(String operation, List<sObject> data, Boolean allOrNothing) {
    List<sObject> toDoNow = new List<sObject>();
    List<Deferred_DML__c> toDefer = new List<Deferred_DML__c>();
    String sObjectTypeName;

    for(Integer i=0; i < data.size(); i++) {
        if(i < BATCH_SIZE) {
            toDoNow.add(data[i]);
        } else {
            if(sObjectTypeName == null) {
                sObjectTypeName = data[i].getSObjectType().getDescribe().getName();
            }
            toDefer.add(new Deferred_DML__c(JSON_to_Commit__c = JSON.serialize(data[i]), 
                                            Operation__c = operation,
                                            sObjectType_Name__c = sObjectTypeName,
                                            Status__c = 'New'));
        }
    }

    if(!toDefer.isEmpty()) {
        insert toDefer;
    }
    if(!toDoNow.isEmpty()) {
        return doOperation(operation, toDoNow, allOrNothing);
    }
    return new List<Database.SaveResult>();
}

If you decide to take this approach, you can probably join the dots yourself.