Apex governor limit warning - no sign of hitting limit

Question

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:

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

Answer 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?