System limit exception too many soql queries 101

In Salesforce we can encounter the system limit exception too many soql queries 101. In this blog, we’re going to look at what this error means and how we can go about solving it.

What does system limit exception too many soql queries 101 mean?

Salesforce is a multi-tenant cloud-based environment. This means that there are many organizations using the same resources(databases, servers etc.). Salesforce needed a way to ensure that a single organization does not eat up a lot of their valuable processing power. This is why Salesforce introduced Governor Limits.

system limit exception too many soql queries 101 is a governor limit set by Salesforce. This is a hard limit, contacting salesforce support to increase this limit will not work.

In simple terms, the error message system limit exception too many soql queries 101 means that a single apex transaction has issued more than 100 SOQL queries. That is a whole lot of queries. 

What are some scenarios that the system limit exception too many soql queries 101 can appear

• An automated process in Salesforce is firing complex logic
• This includes all of the salesforce automation capabilities. Triggers, batches, flows, workflows, process builder etc.
• A trigger is issuing SOQL queries in a loop
• A trigger has not been bulkified
• A trigger is getting fired recursively

An example where system limit exception too many soql queries 101 is fired

Let’s look at some sample code where the system limit exception too many soql queries 101 gets fired. 

Scenario 1: SOQL query issued in a for loop

If you receive the system limit exception too many soql queries 101 error then this is the first thing you should look for. Any SOQL queries issued within a for loop is sure to cause you issues at some point.

Here’s some sample code that calls a SOQL query on an account. You can issue this by using anonymous apex in the saleforce developer console.

for(Integer i = 0; i < 101; i++){
   Account a = [SELECT Id FROM Account LIMIT 1];
}

When we run the above code the error System.LimitException: Too many SOQL queries: 101 will be thrown. The reason it is thrown is that we are issuing a SOQL query on each iteration of the loop. This loop will execute more than 100 times. The total number of SOQL queries issued will be greater than 100. This is a very simple scenario but I’ve often seen similar code in orgs.

Scenario 2: A trigger is not bulkified

This is another scenario where you’ll often see System.LimitException: Too many SOQL queries: 101 thrown. A bulkified trigger ensures that your code can handle the processing of multiple records at once. You can read more about bulkification here.

Let’s see an example where a trigger is not bulkified and throws the System.LimitException: Too many SOQL queries: 101 error. 

The following unbulkified trigger will fire on before insert of a contact. The trigger should take the contact’s account.Name field and stamp it on the contact.LastName field.

trigger ExampleTrigger on Contact (before insert) {
   if (Trigger.isInsert) {
       for(Contact c : Trigger.new){
           Account contactsAccount = [SELECT Id, Name FROM Account WHERE Id =: c.AccountId];
           c.LastName = contactsAccount.Name;
       }
   }
}

The above code is not bulkified so if we process a number of contact records in one transaction the System.LimitException: Too many SOQL queries: 101 will be thrown. This is a regular enough issue that occurs if an admin is trying to upload records using the data loader or the data upload wizard. 

Issue the following script in anonymous apex to check that the error is thrown

Account account = [SELECT Id FROM Account LIMIT 1];
List<Contact> contacts = new List<Contact>();

for(Integer i = 0 ; i < 101; i++){
   contacts.add(
           new Contact(
                   AccountId = account.Id,
                   FirstName = 'John'
           )
   );
}

insert contacts;

Now, this is the error we receive: Error on line 4, column 1: System.LimitException: Too many SOQL queries: 101 Trigger.ExampleTrigger: line 4, column 1

The error is telling us that the ExampleTrigger issued too many SOQL queries. 

How would we solve the above problem? We would just bulkify the trigger. The solution would look something like the following

trigger ExampleTrigger on Contact (before insert) {
   if (Trigger.isInsert) {

       /*Collect all of the account ids in a set,
       this will allow us to issue a query on all accounts at once*/
       Set<Id> accountIds = new Set<Id>();

       //Use a for loop to collect the account Ids we need for a bulk query
       for(Contact c : Trigger.new){
           if(c.AccountId != null) {
               accountIds.add(c.AccountId);
           }
       }

       //Always add checks to reduce the amount of logic fired when not needed
       if(!accountIds.isEmpty()){
           Map<Id, Account> accountMap = new Map<Id, Account>([
                   SELECT Id, Name FROM Account WHERE Id IN: accountIds
           ]);

           /*
               Now we can iterate over the contacts again and
               use our accountMap to set the correct value
            */
           if(!accountMap.isEmpty()){
               for(Contact c : Trigger.new){
                   if(c.AccountId != null && accountMap.containsKey(c.AccountId)){
                       c.LastName = accountMap.get(c.AccountId).Name;
                   }
               }
           }
       }
   }
}

Note

The above trigger is only for illustration purposes. Please use a Trigger Handler

How to find the cause of System.LimitException: Too many SOQL queries: 101

Finding the culprit of the System.LimitException: Too many SOQL queries: 101 can be notoriously difficult. In some cases, it may take you hours to find out the root cause. Trust me, it’s very satisfying when you finally find it. 

Here are some tricks I use to find the cause of the System.LimitException: Too many SOQL queries: 101 error.

• Use the details in the error message
• Use the developer console’s analysis perspective

Use the details in the error message - How to debug a trigger in Salesforce

The error message can sometimes point you in the right direction. You can at least work your way back from the location that the error was thrown. In our example above our error message was  Error on line 4, column 1: System.LimitException: Too many SOQL queries: 101

Trigger.ExampleTrigger: line 4, column 1. This is the first piece of information we need to see how to debug a trigger in salesforce. This tells us where to start looking. The error, in this case, occurred in the trigger ExampleTrigger on line 4. Our first check should be to look there. If there are no loops etc in that area you’ll need to work your way back in the code to find the issue. 

Use the developer console’s analysis perspective

This is a very underutilized tool. This should be the first thing we open whenever we encounter a limit exception in Salesforce. We’re going to use our example from above where the error thrown was Error on line 4, column 1: System.LimitException: Too many SOQL queries: 101 Trigger.ExampleTrigger: line 4, column 1. 

Go ahead and open the developer console. Run the script from the example in Scenario 2: A trigger is not bulkified. A debug log should be captured. Double click to open it up.

Solving more complex scenarios 

This is not an easy task. If you’ve looked for SOQL queries in loops and haven’t found any culprits then you may have a more series issue in your org. 

I would consider the following scenarios to be more complex and require more re-work to solve the System.LimitException: Too many SOQL queries: 101

• Too much logic is being fired
• There is a recursive issue in your code 

Solving System.LimitException: Too many SOQL queries: 101 when too much logic is being fired

This could turn into a huge undertaking. Let’s say you encounter the Too many SOQL queries: 101 error when a case record is inserted in salesforce. If you can’t find any obvious reasons(like a SOQL query in a for loop) then you’ll need to document all of the processes that fire when a case gets inserted. You’ll need to determine which processes are required and which processes are no longer needed. You should be able to reduce the logic firing when the case is inserted by following these steps. If it turns out that the business still needs all of the processing to occur then you’ll need to try and make the code more efficient by reducing the amount of SOQL queries issued. This is a very unique undertaking in each org and will require a skilled developer.

In short, try and follow these steps:

1. Document all of the business logic that fires when the record is inserted
2. Identify any business logic that is no longer needed
3. Make current business logic more efficient by enhancing the current code
4. Identify any after triggers that could potentially become before triggers
5. Try and eliminate the use of workflows for updating field values. This causes additional DML statements to be issued 
6. Potentially move some business logic into future https://www.levelupsalesforce.com/future-method-in-salesforce or queueable methods

Solving System.LimitException: Too many SOQL queries: 101 when a recursive issue is in your code

This is actually simpler than the above problem. You can find recursive issues by checking a debug log and seeing if the same code is fired twice. A simple way to do this is to add a System.debug statement in your code and see if it shows up twice in a debug log.

Common causes of recursive triggers include the following:

1. A workflow rule updates a field value - this will cause an Apex trigger to re-fire
2. A future or queuable method issues an update on the same record that called them

You should take the following steps to try and solve the problem:

1. Try and identify where the recursion is taking place
2. Once and identified, try and simplify the code to avoid the recursive call
3. Avoid using workflow field updates
1. Use before Trigger or before flows instead
4. Use a static Boolean in Apex to limit your code to only firing one time. 

I hope you now understand the cause of the System.LimitException: Too many SOQL queries: 101 error. You should have some more tools in your developer kit to go out there and solve this error.