When working with Salesforce, you must work within a set of limits for each transaction called Governor Limits. One limit that is breached more often than others relates to the number of SOQL executions; a limit where an execution cannot perform more than 100 SOQL database queries. These queries are typically consumed by triggers which process the creation or updating of records like contacts, campaigns, and opportunities.

When a notification is processed by MoveData, it will execute in a number of phases. For donations, this will be to create/update all accounts, followed by contacts, campaigns, recurring donations and then opportunities / donation records. Each of these phases will execute a small number of SOQL and DML statements to gather information and complete the "upsert" of a record.

When a records is updated or created, it isn't uncommon that a Salesforce instance will have a number of record-based actions that will run. These actions can be Process Builder jobs, Lightning Flows, Workflow Rules, Apex Triggers, Trigger Handlers and the like.

The above diagram illustrates the compounding nature of an insert or an update. In this example, a MoveData contact flow has run and is resulting a contact record being "upserted". This action results in any workflows, Lightning Flows, Process Builder jobs and Apex triggers running.

This is a normal behaviour; where this becomes problematic is when there are a large suite of record-triggered actions connected.

Typically, each of these actions has to:

It's easy to see how this can compound and quickly become an issue completing all required tasks within the Salesforce governor limits.

MoveData authors a number of extension packages that integrate with specific data models, such as the Non-Profit Success Pack (NPSP) and Non-Profit Cloud (NPC). These are optimised out-of-the-box to ensure they produce a low number of SOQL and DML executions.

We cannot provide specific numbers on SOQL and DML executions as these very depending on the data being processed, we have included a complex example as a point of reference.

Using the out-of-the-box NPSP extensions, the above transaction results in three contacts, three campaigns, a recurring subscription with a child donation.

This complex transaction requires 32 SOQL queries and 12 DML statements.

Due to the diverse system-wide contributors to this issue, there is no universal method to address the problem. This section will talk through the contributors to this issue.

Salesforce Workflows are a legacy approach to implement business rules. These were superseded by Salesforce Flows. These actions aren't especially efficient but really put a dent into a transaction's limits when used at scale. These should be migrated to Lightning Flows or, if required, Apex code.

We would note that if you use the Workflow to Flows migration tool, you will still need to optimise the resulting flows.

Process Builder was the precursor to Lightning Flows. It is straightforward to use; however, this ease of use results in a large number of unnecessary requests. These should be migrated to Lightning Flows or, if required, Apex code.

When well written, Apex triggers (along with Apex code in general) are the most efficient business logic solution you can implement. When done badly, they suffer from similar inefficiency as the discussed workflow options. Often, this code will belong to a managed package and will not be visible. The best way to gauge the efficiency of a trigger is to monitor is via the debug log, which is discussed later on.

There are many permutations to Lightning Flows but the contributor to this SQL 101 issue are record-triggered flows. When configured to run when a record is created and/or updated, these will use up limits within the transaction.

There are methods to minimise this impact. Using record-triggered flow configured for Fast Field Updates, will give you less functionality but have a minor impact on limits.

Even better is flagging the flow to Run Asynchronously. This will ensure the flow runs in a seperate transaction after the current transaction has successfully completed. The downside is that if the asynchronous flow fails, it will not be able to rollback the original transaction.

Where possible, applying Entry Conditions is highly recommended. This will prevent flows executing in every scenario, conserving limit resources.

One package that can considerably impact the SQL 101 issue is Declarative Lookup Rollup Summaries (dlrs). More on this can be found in this knowledge base article.

The primary way to diagnose SQL 101 issues is to use the Debug Logs. If you have a notification in MoveData that consistently triggers a SQL 101 error, we would recommend you setup a Debug Log via Setup (Environments -> Logs -> Debug Logs). More on how to work with Event Logs can be found here and on Salesforce Trailhead.

Once debugging is setup, open the failing MoveData notification and click Reprocess. Once the notification has failed, return to the debug log and view a log entry with the operation named /movedata/movedataHandler.

Within the debug logs, you can work top to bottom reviewing how the limits are increasing throughout the transaction grouped by package / namespace. Look for the LIMIT_USAGE_FOR_NS entry in the logs. This is the most important metric for seeing where the limits are jumping (and in which package which can be helpful).

Other useful entries are FLOW_CREATE_INTERVIEW; these detail when a Lightning Flow begins and end. Entries beginning with CODE_UNIT will often refer to Apex triggers and entries beginning with WF_ refer to legacy Workflow jobs.

Stepping through these entries and monitoring the limits will enable you to figure out where the jumps in limits are occurring.

Below details how to configure some MoveData flows to run asynchronously. This can be useful in reducing limits within the transaction.

It is possible to configure the MoveData Campaign Post and Donation Post Flows to run asynchronously. Enabling this moves these Flows (and associated Extension Flows) out of the MoveData execution and into their own transaction, which can provide SOQL relief in the form of:

In a practical sense, this can provide SOQL relief where your processes are creating or updating records on the basis of Campaign, Campaign Member, or Opportunity records being created or updated.