"Too many SOQL queries: 201" while running batch - how to fix this code?

Question

I want to test my method and I have this error:

FATAL_ERROR|System.LimitException: Too many SOQL queries: 201
Class.PatchPanelTriggerClass.deleteDuplicates: line 202, column 1
Class.PatchPanelTriggerClassTestBatch.execute: line 19, column 1

I know that I have method that runs SOQL in for loop (in batch) but I don't know how to fix it.

This is my method to test:

public static void deleteDuplicates(String patchPanelName) {
    patchPanelName = patchPanelName + '%';

    Map<String, Interface__c> records = new Map<String, Interface__c>();
    List<Interface__c> duplicates = new List<Interface__c>();

    // change it for all PatchPanels which starts with certain patchPanelName
    for (Interface__c record : [SELECT Name, Status__c FROM Interface__c where Name like :patchPanelName limit 50000]) { // Class.PatchPanelTriggerClass.deleteDuplicates: line 202, column 1

        // take duplicates to delete
        Interface__c temp = records.put(record.Name, record);

        // put duplicates to remove them
        if (temp != null && temp.Status__c != 'Live') {
            duplicates.add(temp);
        }
    }
    delete duplicates;
}

This is my batch:

global with sharing class PatchPanelTriggerClassTestBatch implements Database.batchable<SObject>, Database.Stateful {

public Boolean flag = false; 
@TestVisible static Boolean flagForTest  = false;

global void execute(Database.BatchableContext param1, List<Patch_Panel__c> param2) {

    flag = true;

    for (Patch_Panel__c p : param2) {
        PatchPanelTriggerClass.deleteDuplicates(p.Name); // Class.PatchPanelTriggerClassTestBatch.execute: line 19, column 1
        List<Interface__c> records = [SELECT Name, Id, Status__c, Port_No__c, Rack__c, Show_in_Customer_Portal__c FROM Interface__c where Name LIKE :p.Name]; // Too many SOQL queries: 201
        if (thereAreNoDuplicates(records) == false) {
            flag = false;
        }
    }
}

// just a method which don't do any SOQL
private static Boolean thereAreNoDuplicates(List<Interface__c> records) {
    Set<String> setOfInterfaces = new Set<String>();
    for (Interface__c i : records) {

        if (setOfInterfaces.contains(i.Name)) {
            return false;
        }
        setOfInterfaces.add(i.Name);
    }
    return true;
}

global void finish(Database.BatchableContext param1) {
    flagForTest = flag;

}

global Database.QueryLocator start(Database.BatchableContext param1) {
    String s = 'select Name, Total_No_of_Ports__c from Patch_Panel__c limit 160'; // todo1

    return Database.getQueryLocator(s);
}
}

How to fix it?

Answer 1

You should not be calling the deleteDuplicates method from within the for loop.

for (Patch_Panel__c p : param2) {
    PatchPanelTriggerClass.deleteDuplicates(p.Name); // Class.PatchPanelTriggerClassTestBatch.execute: line 19, column 1
  

I suggest you either change the calling code or else accept the collection in deleteDuplicates and modify the SOQL to use in rather than like for your Name filter.

Answer 2

The issue is that, with a large enough batch size, you will exceed the SOQL query limit because you are executing a query inside of a for-loop inside execute():

for (Patch_Panel__c p : param2) {
    PatchPanelTriggerClass.deleteDuplicates(p.Name); 
    List<Interface__c> records = [
        SELECT Name, Id, Status__c, Port_No__c, Rack__c, Show_in_Customer_Portal__c 
        FROM Interface__c 
        WHERE Name LIKE :p.Name
    ]; /* <-- bad practice, exceeds limit */

    if (thereAreNoDuplicates(records) == false) {
        flag = false;
    }
}

Even with a batch size of 100, you will execute 200 queries (at least) within this batch (query inside execute, another query called in deleteDuplicates for each iteration). You should aim for cutting it down to 2 queries by bulkifying them and avoid running them inside loops.

You should look into ways to limit the amount of queries you're doing, most likely by leveraging a Map that relates Patch_Panel__c records or IDs to a List of Interface__c records. There are no ways around the governor limits, they exist for a reason.

You may want to reconsider using the LIKE operator, which is useful for doing partial text matching in SOQL queries, but you're not using any wildcards in your search. The only thing LIKE is doing for you in this query is making the Name comparison case insensitive.

Additionally, you run the risk of exceeding DML limits by calling deleteDuplicates inside of a for-loop. You should add your duplicates to a collection within the loop and then only call deleteDuplicates one time, preferably by adding the Interface record IDs into a collection rather than querying based on Name.