Anonymous Apex - System.LimitException: Too many SOQL queries: 101 - No DML In LOOP?

Question

I am getting the "Too Many SOQL queries" error on the code below when executing as anonymous APEX in my sandbox. But by my reading of "best practices" I thought I'm doing things right - I was careful not to do DML inside any loop. The list I'm trying to update is about 4000 records.

What am I doing wrong? How can I fix it?

List<Case> caseList = [
    SELECT ID
    ,CASENUMBER
    ,ISCLOSED
    ,CreatedDate
    ,CLOSEDDATE
    ,status
    ,Request_Received_Date_Time__c
    ,Case_Resolution_Date_Time__c
    ,Request_To_Resolution_Business_Hours__c
    FROM CASE 
    WHERE ISCLOSED = true
    and calendar_year(createddate) = 2018
    AND Request_To_Resolution_Business_Hours__c = NULL
];

System.debug('caseList Size: ' + caseList.size()); // that list is going to be about 4000 records for 2018

BusinessHours bh = [
    SELECT FridayEndTime        
        ,FridayStartTime
        ,Id
        ,IsActive
        ,IsDefault
        ,LastModifiedById
        ,LastModifiedDate
        ,MondayEndTime
        ,MondayStartTime
        ,Name
        ,SaturdayEndTime
        ,SaturdayStartTime
        ,SundayEndTime
        ,SundayStartTime
        ,SystemModstamp
        ,ThursdayEndTime
        ,ThursdayStartTime
        ,TimeZoneSidKey
        ,TuesdayEndTime
        ,TuesdayStartTime
        ,WednesdayEndTime
        ,WednesdayStartTime FROM BusinessHours where Name='xxxxx'
    LIMIT 1
];

For(Case case_record : caseList){
    try{        
        DateTime startDate, endDate;
        if(case_record.Request_Received_Date_Time__c!=null) { startDate = case_record.Request_Received_Date_Time__c; }
            else{ startDate = case_record.CreatedDate; }                    
        if(case_record.Case_Resolution_Date_Time__c!=null) { endDate = case_record.Case_Resolution_Date_Time__c; }
            else{ endDate = case_record.ClosedDate; }


        Double requestToResolution = BusinessHours.diff(bh.Id, startDate, endDate)/3600000.0;

        case_record.Request_To_Resolution_Business_Hours__c = requestToResolution;

        System.debug('Case ' + case_record.CASENUMBER + ' Request_To_Resolution_Business_Hours__c updated to: ' + requestToResolution);

    } catch(Exception e) {
        System.debug('General Unhandled Exception on case ' + case_record.CASENUMBER + ' ' + e.getMessage());
    }
}

Update caseList;

Answer 1

@kurunve has a point, if you have trigger on Case sObject and you're retrieving about 4k rows, then it definitely can exceed the limit of SOQL queries in one transaction.

Workaround: Process your records in several transactions:

List<Case> caseList = [
    SELECT ...
    FROM CASE 
    WHERE ISCLOSED = true
    and calendar_year(createddate) = 2018
    AND Request_To_Resolution_Business_Hours__c = NULL
    LIMIT 1000 OFFSET 0
];

Take a constant limit (increase or decrease it in a way that suits you) and change offset for the next execution.