Too many SOQL Queries when inserting records

Question

I have an apex class that implements SandboxPostCopy, and runs every time we create a sandbox. in that process we insert sample data from a few static resources, one static resource for each record: Account, Opportunity, Contact, Lead

This part of the script looks like this:

insertRecords('Account','SandboxManagement_SampleData_Account_PartnerRT');
insertRecords('Lead','SandboxManagement_SampleData_Lead');
insertRecords('Contact','SandboxManagement_SampleData_Contact');

The problem is whenever it runs, i receive the error Too Many SOQL Queries, because all of the triggers are fired.

How can i overcome this issue? i thought about chaining Queueable jobs for each record, but feels like there is a better solution i am missing.

Answer 1

If you're running in to governor limits because of SOQL, you will need to break up the transaction in to multiple transactions. Queueable is actually ideal for this scenario, although you could also just as easily use @future (to keep all the methods in the same class), or batchable (overkill, but a valid solution for a one-off process).

The alternative is to have a "trigger killer" function built in to your triggers. For example, in our org, our code would look more like this:

TriggerFramework.disableAll();
insertRecords('Account','SandboxManagement_SampleData_Account_PartnerRT');
insertRecords('Lead','SandboxManagement_SampleData_Lead');
insertRecords('Contact','SandboxManagement_SampleData_Contact');

Where TriggerFramework has a few static variables to check the transaction state, and each trigger has logic like the following:

if(TriggerFramework.isTriggerDisabled('Account')) {
  return;
}
AccountTriggerHandler.execute(Trigger.old, Trigger.new);

As you might imagine, the code is a little more complicated with all the pieces we have in place, but hopefully this answer should give you enough details to go off of.

Answer 2

There's no guaranteed or magic-bullet solution, although the solution pattern you adopt will certainly vary based on the total data volume that's involved in this process and on your trigger architecture.

If you're inserting a relatively small number of records (fewer than 200 per sObject), there's a good chance your triggers and/or Process Builder/Flow automations are in need of optimization, as they're over-consuming SOQL.

If you're inserting a somewhat larger number of records, optimization might get you there, but you also may simply be trying to load more data in a single transaction than your system is able to accept. In that case, you'd want to split these loads into multiple transactions. A first pass would be to go to a single pass per sObject; depending on the data volume, you may need to go to a batch class or Queueable chain per sObject.

Lastly, if your data is already in "final form" - it doesn't require trigger operation to yield a complete data set that maintains the integrity and invariants you expect - and your org is designed with some method of deactivating your triggers, such as a Custom Setting, you could simply turn them off during the load.

Answer 3

The best way to handle this is to create a custom setting that could be checked and unchecked when triggers should be skipped or not.