Diagnosing and Fixing Apex Batch "Too Many SOQL Queries" Errors

Recognizable Condition

When a batch job fails with the runtime error Too many SOQL queries: 101, the transaction has executed more than the allowed 100 SOQL queries. In the debug log you will see the line:

System.LimitException: Too many SOQL queries: 101

Typical symptoms: the batch stops after a few executions, the batch apex class throws an exception, and the batch status in the Apex Jobs page shows Failed.

Common Causes

The following patterns are the most frequent culprits:

• SOQL inside a for loop in execute() – one query per record.
• Large batchSize (e.g., 2000) – many records per transaction, so each query is multiplied.
• Trigger‑side SOQL that is not bulkified – each trigger invocation adds a query.
• Recursive trigger execution on the same object during batch processing.
• Missing use of @future or Queueable to offload heavy queries.

Diagnostic Table

Finding	Likely Cause	Check
Query count spikes after first 200 records	Query inside loop	Search for SOQL inside for loops in execute()
Batch fails only when batchSize > 500	Large batch size amplifying queries	Review Database.executeBatch call
Trigger logs show >100 queries per transaction	Unbulkified trigger	Examine trigger code for per‑record queries
Stack trace shows repeated MyObjectTrigger calls	Recursive trigger	Check for self‑inserting/updating within trigger
Batch runs but data is incomplete	Missing async offload	Verify expensive queries are moved to @future or Queueable

Ordered Checks

1. Inspect the Batch Class
   Open the batch class (e.g., MyBatchJob) and locate the execute() method. Search for any SELECT statements that appear inside a for loop that iterates over scope or any collection.
2. Check batchSize
   Look at the Database.executeBatch call. If the size is >500, consider reducing it to 200 or 100 for testing. Note that a smaller size will increase the number of executions but keep each transaction within limits.
3. Audit Triggers
   Open any triggers that fire on the objects processed by the batch. Look for SOQL in before or after blocks that run per record. Use the “Bulk‑Friendly” pattern: collect IDs first, then run a single query.
4. Detect Recursion
   In the trigger, search for DML on the same object it is defined on. If found, add a static flag to prevent re‑entry.
5. Review Async Offload
   Check if the batch calls any @future or Queueable classes that perform SOQL. Ensure those calls are batched themselves or use Database.executeBatch with a smaller size.
6. Examine Debug Logs
   Run the batch with Debug Level: Apex Code, Apex Profiling. Look for the SOQL: SELECT lines and count them per transaction. The log will show SOQL: SELECT… followed by SOQL: SELECT… for each query.

Fixes Tied to Findings

• Move Queries Out of Loops
  Replace per‑record queries with a single query that collects all needed data. Example:

  // Bad
  for (Account a : scope) {
      List con = [SELECT Id FROM Contact WHERE AccountId = :a.Id];
      // ...
  }
  // Good
  Set acctIds = new Set();
  for (Account a : scope) acctIds.add(a.Id);
  Map> conMap = new Map>();
  for (Contact c : [SELECT Id, AccountId FROM Contact WHERE AccountId IN :acctIds]) {
      conMap.putIfAbsent(c.AccountId, new List());
      conMap.get(c.AccountId).add(c);
  }
  // ...
  

• Reduce batchSize
  If you cannot bulkify immediately, temporarily set batchSize to 200 and rerun. Verify the query count remains below 100.
• Bulkify Triggers
  Apply the same pattern as above inside the trigger. Use Database.query only once per transaction.
• Add Static Flag for Recursion
  Add a static Boolean flag in the trigger class:

  public class MyTriggerHandler {
      public static Boolean isRecursive = false;
  }
  // In trigger
  if (!MyTriggerHandler.isRecursive) {
      MyTriggerHandler.isRecursive = true;
      // DML logic
      MyTriggerHandler.isRecursive = false;
  }
  

• Offload Heavy Queries
  Move expensive queries to a Queueable class and enqueue it from the batch. Example:

  public class HeavyQueryJob implements Queueable {
      public void execute(QueueableContext ctx) {
          // Complex SOQL
      }
  }
  // In batch
  Database.executeBatch(new HeavyQueryJob(), 1);
  

Escalation Criteria

1. After applying all fixes, the batch still fails with Too many SOQL queries.
2. Debug logs show more than 100 SOQL queries even after reducing batchSize to 200.
3. There are more than 5 concurrent batch jobs running; the governor limit is hit across jobs.
4. The batch performs data‑critical operations and cannot be split further.

Practical Verification Example

Assume you have a batch that updates Opportunity records based on related Account data. The original class:

global class OpportunityUpdateBatch implements Database.Batchable {
    global Database.QueryLocator start(Database.BatchableContext bc) {
        return Database.getQueryLocator('SELECT Id, AccountId FROM Opportunity');
    }
    global void execute(Database.BatchableContext bc, List scope) {
        List opps = (List)scope;
        for (Opportunity o : opps) {
            Account a = [SELECT Industry FROM Account WHERE Id = :o.AccountId LIMIT 1];
            o.Custom_Field__c = a.Industry;
        }
        update opps;
    }
    global void finish(Database.BatchableContext bc) {}
}

Diagnostic steps:

1. Run the batch with batchSize = 2000 → fails after first execution.
2. Inspect execute() → SOQL inside loop.
3. Rewrite as bulkified query (shown above). Re‑run with batchSize = 2000 → success.

Verify by checking the debug log: you should see only 1 SELECT per transaction, not one per Opportunity.

Summary

When an Apex batch hits the SOQL query limit, the root cause is almost always a per‑record query or an unbulkified trigger. Use the diagnostic table to pinpoint the issue, apply the corresponding fix, and validate with debug logs. If the problem persists after all fixes, bring the case to a senior developer or Salesforce support, noting the concurrency and data‑critical nature of the job.