0

I'm receiving the following email every time I view a case:

Operation: /apex/caseTiming

By user/organization: 005a000000BTWwZ/00D30000000omsI

Caused the following Apex resource warnings:

Approaching limit for non-selective query against large object type (more than 100000 rows). Current size is approximately 78744 rows. When the limit is reached, the query will fail. Consider an indexed filter or contact salesforce.com about custom indexing. Even if a field is indexed a filter might still not be selective when: 1. The filter value includes null (for instance binding with a list that contains null) 2. Data skew exists whereby the number of matching rows is very large (for instance, filtering for a particular foreign key value that occurs many times)

We have 78797 cases, which I assume corresponds to the 'approximately 78744' rows mentioned in the email.

The code for caseTiming.apxc is here:

public class caseTiming {
    decimal timeDiff = 0;        
    decimal timework = 0;
    decimal timePE = 0;
    DateTime now = system.now();
    BusinessHours ukBusinessHours = [select id from businesshours where isDefault = True];
    BusinessHours usBusinessHours = [select id from businesshours where isDefault = False];


    // When called from bulk
    public void bulkCalc(List<Case> cases){
        for (case cs : cases){
            calcAllTime(cs);
        }
    }

    // Before update. Calculate latest relevant timings
    public void calcAllTime(Case cs){

        // Build variables
        string hoursId = cs.BusinessHoursId;
        DateTime startGB = cs.Time_Start_GB_hours__c;
        DateTime startUS = cs.Time_Start_US_hours__c;
        DateTime startPE = cs.Time_start_pending_external__c;
        DateTime startCS;


        //Only run on new cases
        if (startGB != null){

            // Check whether case has been reopened
            if (cs.Time_Re_Opened__c == null){
                startCS = cs.CreatedDate;
            }
            else {
                startCS = cs.Time_Re_Opened__c;
            }

            // Always update open time
            system.debug('startCS = ' + startCS);
            system.debug('now = ' + now);
            timeDiff = Math.Floor( double.valueOf(( (now.getTime() - startCS.getTime()) / (1000.0) ) 
                                                 ));
            cs.Open_Time_calc__c = timeDiff;


            // Update time when pending external
            If (cs.Status == 'Pending External' && cs.Time_start_pending_external__c != null){
                pendingExternal(cs, startPE);
            }

            // Update Work Time
            if (hoursId == ukBusinesshours.Id) {
                timework = ( BusinessHours.diff(ukBusinessHours.Id, startGB, now) / 1000 ) 
                    ;
                cs.Work_Time_GB_calc__c = timework;
            }
            else {
                if(startUS != null){
                    timework = ( BusinessHours.diff(usBusinessHours.id, startUS, now) / 1000) 
                        ;
                    cs.Work_Time_US_calc__c = timework;
                }
            }
        }

        if(cs.Priority != 'Low' 
            && cs.Total_Work_Time__c >= 28800 
            && cs.Status != 'Closed' 
            && cs.Status != 'Closed as Duplicate' 
            && string.valueof(cs.OwnerId).substring(0,3) == '005'
            && cs.RecordTypeId == '012a0000000AVO4'
            && (cs.OwnerId == '00530000004V2cA' 
                            || cs.OwnerId == '00530000004V2cK'
                            || cs.OwnerId == '00530000004V2ce'
                            || cs.OwnerId == '00530000004V2cj'
                            || cs.OwnerId == '00530000004V2d3'
                            || cs.OwnerId == '00530000004XJ6H'
                             )
                             )
                             {
               emailAlerts(cs);
           }
    }


    // Pending External
    public void pendingExternal(Case cs, DateTime startPE){
        if(cs.Time_start_pending_external__c != null){
            timeDiff = Math.Floor( double.valueOf(( (now.getTime() - startPE.getTime()) / (1000.0) ) 
                                                 ));
            cs.Pending_External_Calc__c = timeDiff;

            timeDiff = ( BusinessHours.diff( cs.BusinessHoursId, startPE, now ) / 1000 )
                ;
            cs.Work_Time_PE_calc__c = timeDiff;
        }
    }


    // setup for new time conditions
    public void statusChange (string change, Case cs){

        Datetime now = system.now();

        if (change == 'c2o'){
            // Closed to Open
            cs.Open_Time_prev__c = cs.Open_Time__c;
            cs.Time_Re_Opened__c = now;
        }
        if (change == 'o2p'){
            // Open to Pending External
            cs.Time_Pending_Previous__c = cs.Pending_External__c;
            cs.Work_Time_PE_prev__c = cs.Work_Time_PE__c;
            cs.Time_start_pending_external__c = now;
        }
        if (change == 'gb2us'){
            // GB to US
            cs.Work_Time_US_prev__c = cs.Work_Time_US__c;
            cs.Time_start_US_hours__c = now;
        }
        if (change == 'us2gb'){
            // US to GB
            cs.Work_Time_GB_prev__c = cs.Work_Time_GB__c;
            cs.Time_start_GB_hours__c = now;
        }
    }

    public void emailAlerts(Case cs){
        // need to be soql queries because triggers don't support foreign relationship links.
        String ccrecipient = 'john.smith@email.com'; 
        String ownerName = [select Name from User where id =: cs.OwnerId].Name;
        String priority = cs.Priority;
        String caseNumber = cs.CaseNumber;
        Id Owner = cs.OwnerId;
        Id cId = cs.Id;
            String[] ccRecipients = new String[] {ccrecipient};
                String templateApiName = 'Case_Time_Alert';


        if (cs.Priority == 'Standard'){
            // Select all standard priority cases open for >= 24 hours and < 40 (in seconds)
            if(cs.Total_Work_Time__c >= 86400 && cs.Total_Work_Time__c < 144000){
                if (cs.LastTimeAlertSent__c != 'Standard 24 hours'){

                    sendEmail(ccRecipients, templateApiName, Owner, cId,
                              ownerName, priority, cs.Total_Work_Time__c, caseNumber);

                    cs.LastTimeAlertSent__c = 'Standard 24 hours';
                }
            }

            // Select all standard priority cases open for >= 40 hours and < 80 (in seconds)
            if (cs.Total_Work_Time__c >= 144000 && cs.Total_Work_Time__c < 288000){
                if (cs.LastTimeAlertSent__c != 'Standard 40 hours'){

                    sendEmail(ccRecipients, templateApiName, Owner, cId,
                              ownerName, priority, cs.Total_Work_Time__c, caseNumber);

                    cs.LastTimeAlertSent__c = 'Standard 40 hours';   

                    system.debug('Case ' + cs);
                }
            }

            // Select all standard priority cases open for >= 80 hours (in seconds)
            if (cs.Total_Work_Time__c >= 288000){
                if (cs.LastTimeAlertSent__c != 'Standard 80 hours'){

                    sendEmail(ccRecipients, templateApiName, Owner,
                              cId, ownerName, priority, cs.Total_Work_Time__c, caseNumber);

                    cs.LastTimeAlertSent__c = 'Standard 80 hours';
                }
            }
        }

        if (cs.Priority == 'High'){
            // Select all high priority cases open for >= 8 hours and < 16 (in seconds)
            system.debug('test case high ' + cs);
            if (cs.Total_Work_Time__c >= 28800 && cs.Total_Work_Time__c < 57600){
                if (cs.LastTimeAlertSent__c != 'High 8 hours'){

                    sendEmail(ccRecipients, templateApiName, Owner,
                              cId, ownerName, priority, cs.Total_Work_Time__c, caseNumber);

                    cs.LastTimeAlertSent__c = 'High 8 hours';
                }
            }

            // Select all high priority cases open for >= 16 hours and < 24 (in seconds)
            if (cs.Total_Work_Time__c >= 57600 && cs.Total_Work_Time__c < 86400){
                if (cs.LastTimeAlertSent__c != 'High 16 hours'){

                    sendEmail(ccRecipients, templateApiName, Owner,
                              cId, ownerName, priority, cs.Total_Work_Time__c, caseNumber);

                    cs.LastTimeAlertSent__c = 'High 16 hours';
                }
            }

            // Select all high priority cases open for >= 24 hours and < 40 (in seconds)
            if(cs.Total_Work_Time__c >= 86400 && cs.Total_Work_Time__c < 144000){
                if (cs.LastTimeAlertSent__c != 'High 24 hours'){

                    sendEmail(ccRecipients, templateApiName, Owner,
                              cId, ownerName, priority, cs.Total_Work_Time__c, caseNumber);

                    cs.LastTimeAlertSent__c = 'High 24 hours';
                }
            }

            // Select all high priority cases open for >= 40 hours (in seconds)
            if(cs.Total_Work_Time__c >= 144000){
                if (cs.LastTimeAlertSent__c != 'High 40 hours'){

                    sendEmail(ccRecipients, templateApiName, Owner,
                              cId, ownerName, priority, cs.Total_Work_Time__c, caseNumber);

                    cs.LastTimeAlertSent__c = 'High 40 hours';
                }
            }
        }
    }

    public void sendEmail(String[] ccRecipients, String templateApiName,
                          ID targetObjId, Id whatId, String ownerName, String priority, 
                          decimal Total_Work_Time, String caseNumber)
    {

        integer TWT = integer.valueOf(total_work_time);
        Id templateId;  

        String elapsedTime = string.valueOf(integer.valueOf(math.FLOOR(TWT /3600))) + 
            ':' + string.valueOf(integer.valueOf(math.FLOOR(math.mod(TWT ,3600)/60))) + 
            ':' + string.valueOf(integer.valueOf(math.mod(math.mod(TWT ,3600),60)));

        templateId = [select id, name from EmailTemplate where developername = : templateApiName].id;

        EmailTemplate emailTemplate = [select Id, Subject, HtmlValue, Body, Markup 
                                       from EmailTemplate where Id =: templateId];

        // process the merge fields

        String htmlBody = emailTemplate.Markup;
        string htmlSubject = emailTemplate.Subject;
        htmlSubject = 'Case ' + caseNumber + ' open time';
        htmlBody = htmlBody.replace('{!relatedTo.CaseNumber}', caseNumber);
        htmlBody = htmlBody.replace('{!relatedTo.Owner_Link__r.name}', ownerName);
        htmlBody = htmlBody.replace('{!relatedTo.Priority}', priority);
        htmlBody = htmlBody.replace('{!relatedTo.Elapsed_Business_Time__c}', elapsedTime);      

        Messaging.SingleEmailMessage email = new Messaging.SingleEmailMessage();

        system.debug('ccRecipients ' + ccRecipients + ccRecipients.size());

        email.setSubject(htmlSubject);
//        email.setToAddresses(toRecipients);
        email.setCcAddresses(ccRecipients);
        email.setTargetObjectId(targetObjId);
        email.saveAsActivity = false;
        email.setHtmlBody(htmlBody);

        system.debug('Email to send: ' + email);

        Messaging.sendEmail(new Messaging.SingleEmailMessage[] {email});

    }
}

I've looked the logs in developer console, with profiling set to finest and I see this:

snapshot of execution overview

I also turned on debug logging through setup and set everything to finest, again no sign of being anywhere near the limits. Am I looking in the wrong place, or is something strange happening?

Debug Log:

http://pastebin.com/dntyjDhG

  • In your code, there are only 5 queries and all of them are selective. Can you verify number of users in your org? Additionally, who is invoking this class from your case page? Are you sure there are no other classes getting invoked ? – Salesforcesmarty Feb 2 '16 at 11:46
  • There are 222 users, I should have been clear that 'approximately 78744' given in the email corresponds very closely with our number of cases: 78797. This is being invoked by the visualforce page controller, in the instance above it was me loading the page. There is a third party app called when a case is viewed, but not from this class. – Steve Feb 2 '16 at 11:48
  • Then definitely there is another piece of code is also getting invoked when you are viewing the case. Can you capture the debug log generated? – Salesforcesmarty Feb 2 '16 at 11:50
  • I've added in a debug log – Steve Feb 2 '16 at 13:57
  • @Steve Just so you know you are using up an extra query unnecessarily on your emailTemplate. – Adrian Larson Feb 2 '16 at 17:02
1

You're not hitting any DML limits. What happens is that your query is non-selective. This is a bit of an obscure and complicated topic with Salesforce queries. There's plenty of info and links here, but the one that sums it up the best is the Query & Search Optimization Cheat Sheet.

In short, it's not about the number of DML queries or records returned, but about the number of entries in the database that you're trying to filter out.

From the comments to the question above, it looks like the query that is non-selective is on the case object.

The way to make it selective is to add indexes to the fields you're querying on.

But the first step is to identify exactly what's the query that's failing. Perhaps you're calling your bulkCalc method and supplying it a non-selective query for cases?

| improve this answer | |
  • bulkCalc is only called by a scheduled job which updates all cases once a day. The email alert is always and only delivered when I view an individual case. You're right of course, something somewhere is querying all cases, but without any mention in the logs I'm struggling with how to find it! – Steve Feb 2 '16 at 13:10

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.