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:
emailTemplate. – Adrian Larson♦ Feb 2 '16 at 17:02