0

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;
2
  • 7
    There is a high chance that there is a trigger on Case or related object, which is not able to handle simultaneous update of 4k of records with less then 100 soql queries (basically less then 5 SOQL queries per chunk of 200 records) .
    – kurunve
    Feb 5, 2020 at 22:41
  • Thanks, all. This was exactly it. I found the trigger and did not want to disable/re-enable it so I took the route suggested in the answer - I batched my updates with a LIMIT in the SOQL. Feb 7, 2020 at 20:38

1 Answer 1

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.

1
  • 2
    don't forget that max value for OFFSET is only 2000. Anything above 2000 will result in an QueryException. Based on his code I'd say that Request_To_Resolution_Business_Hours__c = NULL criteria is enough for his needs
    – Novarg
    Feb 6, 2020 at 8:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.