How to Resolve the “Too Many SOQL Queries: 101” Error in Salesforce?

The “Too Many SOQL Queries: 101” error in Salesforce occurs when a piece of Apex code performs more than 100 SOQL (Salesforce Object Query Language) queries in a single transaction. This limit is imposed by Salesforce to ensure efficient use of resources and to prevent individual processes from monopolizing shared resources in a multi-tenant environment.

Here’s an example that might cause this error:

for (Account acc : [SELECT Id FROM Account]) {
    List<Contact> contacts = [SELECT Id, Name FROM Contact WHERE AccountId = :acc.Id];
    // Process contacts
}

In this example, a SOQL query is executed inside a loop. If there are more than 100 accounts, this will result in more than 100 SOQL queries, causing the “Too Many SOQL Queries: 101” error.

Solution:

To solve this issue, you should aim to reduce the number of SOQL queries by using more efficient querying techniques. One common approach is to use bulk SOQL queries and collections to process data in batches. Here’s a revised version of the previous example:

In this revised example, only two SOQL queries are used, regardless of the number of accounts or contacts. This approach significantly reduces the risk of hitting the “Too Many SOQL Queries: 101” error.

Example: Updating Opportunities Based on Related Cases

Suppose you have a requirement to update all related Opportunities when a Case is closed, marking them as “High Priority” if any related Case is marked as “Critical”.

Code That Causes the Error:

for (Case c : [SELECT Id, Status, Priority, AccountId FROM Case WHERE Status = 'Closed']) {
    if (c.Priority == 'Critical') {
        List<Opportunity> relatedOpps = [SELECT Id, Priority FROM Opportunity WHERE AccountId = :c.AccountId];
        for (Opportunity opp : relatedOpps) {
            opp.Priority = 'High';
            update opp;
        }
    }
}

In this code, if there are more than 100 closed Cases, and each Case has at least one related Opportunity, the code will exceed the SOQL query limit.

Solution: Bulkify the Code

To avoid the “Too Many SOQL Queries: 101” error, you can bulkify the code by querying all the necessary data in advance and then processing it in memory:

// Step 1: Find all Account IDs related to closed Cases with 'Critical' priority
Set<Id> accountIds = new Set<Id>();
for (Case c : [SELECT AccountId FROM Case WHERE Status = 'Closed' AND Priority = 'Critical']) {
    accountIds.add(c.AccountId);
}

// Step 2: Bulk update related Opportunities for those Accounts
if (!accountIds.isEmpty()) {
    List<Opportunity> relatedOpps = [SELECT Id, Priority FROM Opportunity WHERE AccountId IN :accountIds];
    for (Opportunity opp : relatedOpps) {
        opp.Priority = 'High';
    }
    update relatedOpps; // Perform a single DML operation outside the loop
}

In this revised solution, only two SOQL queries are used regardless of the number of Cases or Opportunities. This approach significantly reduces the risk of hitting the SOQL query limit.

Here’s a breakdown of what this means and how to address it:

1. Understanding Transactions in Salesforce: A transaction in Salesforce is a sequence of operations that are executed as a single unit. A transaction begins when a piece of Apex code starts executing and ends when all operations (including triggers, SOQL queries, DML operations, etc.) are completed.
2. Governor Limits: Salesforce imposes governor limits to control how much of a shared resource a single transaction can use. One of these limits is the number of SOQL queries that can be executed in a single transaction. The limit is usually 100 queries, but this can vary based on the context (e.g., synchronous vs. asynchronous transactions).
3. Causes of the Error:

• Loops with Queries: A common cause is having SOQL queries inside loops. Each iteration of the loop executes a query, quickly reaching the limit. Here is an example of Updating Contact Records Based on Related Opportunities.
• Multiple Triggers Firing: If multiple triggers are firing due to a DML operation and each trigger performs SOQL queries, they can collectively exceed the limit. Here is an example of Too Many SOQL Queries: Multiple triggers are firing due to a DML operation.
• Complex Operations: Involving many related records and multiple layers of logic can also lead to excessive queries. Here is an example of Too Many SOQL Queries: 101 Error causes with Complex Operations.

Solutions:

• Optimize SOQL Queries: Write queries that return only the necessary data and avoid queries inside loops. Use collections and maps to process data in bulk. Learn more about How to optimize SOQL queries to avoid the “Too Many SOQL Queries: 101”?
• Use Aggregate Functions: When possible, use SOQL aggregate functions to summarize data instead of retrieving all records. Learn more about How to use SOQL aggregate functions to summarize data and avoid Too Many SOQL Queries: 101? errors.
• Limit Trigger Execution: Streamline triggers and ensure they don’t execute unnecessary logic.
• Batch Apex: If the operation is inherently large, consider using Batch Apex, which divides the operation into smaller batches, each with its own set of governor limits. Learn more about How to limit trigger execution to avoid Too Many SOQL Queries: 101 errors?.
• Query More, Query Once: Try to retrieve all necessary data in as few queries as possible. Use relationships (like parent-child subqueries) to get related data in a single query. Learn more about How to use Batch Apex to avoid Too Many SOQL Queries: 101 errors?.

Monitoring and Testing: Utilize Salesforce’s built-in tools like the Developer Console, System Log, and Apex Test Execution to monitor and test your code. Writing efficient test classes can help identify SOQL limit issues before deployment.

Addressing this error requires a careful review of the Apex code to identify where the SOQL queries are being made and optimizing them to stay within the governor limits. It’s part of developing robust and scalable applications on the Salesforce platform.

Seeking to advance your skills in Salesforce CRM administration and development? CRS Info Solutions delivers an exceptional Salesforce certification course tailored for comprehensive learning. Build your knowledge with our detailed, practical Salesforce project sessions. Join now for a complimentary demo and initiate your journey to become a Salesforce expert!