+ Start a Discussion
cpo87cpo87 

Email Message Trigger interacting with installed App and giving too many SOQL Queries Error

I have a trigger that I created on Email Message that populates a date in the parent case of the most recent email message created date.  This allows me to run a workflow that reopens a case if an email comes in after the case has closed.  Here is the code for the trigger.

 

 

trigger LastEmailDateTime on EmailMessage (after insert) { EmailMessage email = Trigger.new[0]; //Populate the case with the Last EmailMessage CreatedDate value for(EmailMessage em:[Select CreatedDate, ParentID from EmailMessage where Id = :email.Id limit 1]){ for(Case c:[Select Id, Last_Email_Date_Time__c from Case where Id = :em.ParentId limit 1]){ c.Last_Email_Date_Time__c = em.CreatedDate; update c; } } }

 

 I recently installed the Case Age in Business Hours app.  Since I did that I am getting script exception errors for too many SOQL queries.  Here is an example of the error I receive.

 

 Developer script exception : LastEmailDateTime : LastEmailDateTime: execution of AfterInsert  caused by: System.Exception: Too many SOQL queries: 22  Trigger.LastEmailDateTime: line 7, column 23

 

Any suggestions on how I can reduce the number of SOQL queries?

 

Thanks!

 

SuperfellSuperfell

You don't need the 2nd query, you can just call update directly

 

trigger LastEmailDateTime on EmailMessage (after insert) { EmailMessage email = Trigger.new[0];//Populate the case with the Last EmailMessage CreatedDate value for(EmailMessage em:[Select CreatedDate, ParentID from EmailMessage where Id = :email.Id limit 1]){ Case c = new Case(Id=em.ParentId); c.Last_Email_Date_Time__c = em.CreatedDate; update c; } 

} 

 

But you do need to do something about the fact that you're only processing one record in your trigger, the Trigger.new array can be larger than one. 

cpo87cpo87

Thanks Simon!  Is that what people are referring to when they talk about bulkifying?  Can you direct me to where I can find directions on how to do this?

 

Thanks,

Christian

mtbclimbermtbclimber

If I'm reading this correctly, you don't need either query but you are going to have to figure out what you want to do in the case where there are two email messages for the same case in one invocation of the trigger - unless the only path to create emailMessage objects prevents this somehow and that behavior is never going to change.

 

A better question might be what is "Case Age in Business Hours app." doing here. Is it really using 20 queries before this ever gets invoked or is it calling insert on EmailMessage 20 times somewhere?

cpo87cpo87

Andrew,

 

I don't quite know what you mean by me not needing either query, I'm still new to apex so I'm open to any suggestion on another way to do this.   If multiple emails cause one invocation of my trigger I just want the most recent email created date to populate into a field on the associated case.  I think the only ways that a new Email Message is created is by Email2Case, through the UI, or through an import into the application.

 

Here is the trigger code on Case Age in Business Hours. There are 3 select queries, two are in for loops.

 

 

trigger CalculateBusinessHoursAges on Case (before insert, before update) {
if (Trigger.isInsert) {
for (Case updatedCase:System.Trigger.new) {
updatedCase.Last_Status_Change__c = System.now();
updatedCase.Time_With_Customer__c = 0;
updatedCase.Time_With_Support__c = 0;
}
} else {
//Get the stop statuses
Set<String> stopStatusSet = new Set<String>();
for (Stop_Status__c stopStatus:[Select Name From Stop_Status__c]) {
stopStatusSet.add(stopStatus.Name);
}

//Get the default business hours (we might need it)
BusinessHours defaultHours = [select Id from BusinessHours where IsDefault=true];

//Get the closed statuses (because at the point of this trigger Case.IsClosed won't be set yet)
Set<String> closedStatusSet = new Set<String>();
for (CaseStatus status:[Select MasterLabel From CaseStatus where IsClosed=true]) {
closedStatusSet.add(status.MasterLabel);
}

//For any case where the status is changed, recalc the business hours in the buckets
for (Case updatedCase:System.Trigger.new) {
Case oldCase = System.Trigger.oldMap.get(updatedCase.Id);

if (oldCase.Status!=updatedCase.Status && updatedCase.Last_Status_Change__c!=null) {
//OK, the status has changed
if (!oldCase.IsClosed) {
//We only update the buckets for open cases

//On the off-chance that the business hours on the case are null, use the default ones instead
Id hoursToUse = updatedCase.BusinessHoursId!=null?updatedCase.BusinessHoursId:defaultHours.Id;

//The diff method comes back in milliseconds, so we divide by 3600000 to get hours.
Double timeSinceLastStatus = BusinessHours.diff(hoursToUse, updatedCase.Last_Status_Change__c, System.now())/3600000.0;
System.debug(timeSinceLastStatus);

//We decide which bucket to add it to based on whether it was in a stop status before
if (stopStatusSet.contains(oldCase.Status)) {
updatedCase.Time_With_Customer__c += timeSinceLastStatus;
} else {
updatedCase.Time_With_Support__c += timeSinceLastStatus;
}

if (closedStatusSet.contains(updatedCase.Status)) {
updatedCase.Case_Age_In_Business_Hours__c = updatedCase.Time_With_Customer__c + updatedCase.Time_With_Support__c;
}
}

updatedCase.Last_Status_Change__c = System.now();
}
}
}
}

 

 Thanks,

 

Christian

 

mtbclimbermtbclimber

I'm saying, based on what I see in your trigger, you don't need to issue any queries because all you want to do is update the related case which you can do like this:

 

 

trigger LastEmailDateTime on EmailMessage (after insert) { Map<Id, Case> caseMap = new Map<Id,Case>(); for(EmailMessage e:Trigger.new) { caseMap.put(e.parentId, new Case(id = e.parentid, Last_Email_Date_Time__c = e.createdDate)); } Database.update(caseMap.values()); }

 

 After more closely reviewing the use case, I don't think there is concern with having multiple in one trigger invocation since the createddate will be the same for all the emailmessage objects.

 

I don't have email2case functionality written and am not familiar with it so I wrote this test class to make sure the above does what is expected:

 

 

@IsTest private class emailCaseTests { static testmethod void basicTriggerTest() { /* Insert a few cases for the test */ List<Case> cases = new List<Case>(); for(Integer i=0;i<10;i++) { cases.add(new Case(Status = 'New', Origin = 'Email')); } Database.insert(cases); /* Now create emailmessage objects for them. */ List<EmailMessage> emails = new List<EmailMessage>(); for(Case c:cases) { emails.add(new EmailMessage(parentId = c.id)); } Test.startTest(); Database.insert(emails); Test.stopTest(); for(Case c:[select last_email_date_time__c, (select createddate from emailmessages) from case where id in :cases]) { System.assertEquals(c.emailMessages.get(0).createddate, c.last_email_date_time__c,'Trigger did not set the date on the case according to the email message.'); } } }

 

As for the CalculateBusinessHoursAges trigger below, there are no queries *within* loops so that should only ever issue 3 SOQL queries. I'm not sure what operation is actually running that is causing the emailMessage trigger to be fired where you saw the error but I this trigger looks fine.

 

 

cpo87cpo87

Thank you Andrew!  I appreciate you showing me a better way to write that trigger.  After changing the trigger and reinstalling the Case Age in Business Hours app I find I am still getting the "Too many SOQL queries:21" error.

 

Apex script unhandled trigger exception 

LastEmailDateTime: execution of AfterInsert 

caused by: System.DmlException: Update failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, CalculateBusinessHoursAges: execution of BeforeUpdate 

caused by: System.Exception: Too many SOQL queries: 21 

Trigger.CalculateBusinessHoursAges: line 20, column 32 

Trigger.LastEmailDateTime: line 7, column 5 

 

Is there some place I can look to find out what is triggering all of these SOQL queries? 

 

Trigger.CalculateBusinessHoursAges: line 20, column 32 is referring to this line... 

 for (CaseStatus status:[Select MasterLabel From CaseStatus where IsClosed=true]) {
            closedStatusSet.add(status.MasterLabel);
        } 

and Trigger.LastEmailDateTime: line 7, column 5 is referring to this line...  

 Database.update(caseMap.values());

mtbclimbermtbclimber

I presume you are testing this by inserting an email messages somehow as the entry point. If so then are there any other triggers on email message? any others on Case?

 

From the your error it seems you are entering this query with 18 queries used up. Something is consuming them.

Message Edited by mtbclimber on 03-02-2010 07:46 AM
mtbclimbermtbclimber
By the way, you can use the System Log (link at the top of the page next to help if you are an admin) to profile your operation for which code artifact is doing which queries. If you're testing this with an API client you can use the debug log under monitoring in setup.
cpo87cpo87

I don't have any other triggers on EmailMessage or Case so I'm not sure what might be doing the queries.  I do have other triggers in my org but they are on account and opportunity.  I haven't been able to figure out what action is creating the script exceptions. 

 

I tried using the system log and so far I haven't been able to get any value from it.  Do I just past a trigger into it and execute?  When I do this I just get an error...

 

13:16:09 INFO - Cumulative profiling information: No profiling information for SOQL operations. No profiling information for SOSL operations. No profiling information for DML operations. No profiling information for method invocations.
13:16:09 ERROR - Compile error: required (...)+ loop did not match anything at input 'trigger'

 

I have added a few users to the debug logs so hopefully the next script exception will show up in my debug logs to help troubleshoot.

 

I'll let you know.

 

Thanks,

 

Christian

cpo87cpo87

Andrew, a script exception has occurred and showed up in my debug logs.  It appears to be started by an Email2Case.  In this exception it looks like 5 cases were created, although I can't find them in the DB under the IDs in the debug log.

 

Here is the order that appears to be happening.

 

Case Trigger Begins - 5 Ids, this accounts for 3 SOQL queries

Case Trigger Ends

Task Trigger Begins

Task Tigger Ends

Email Message Trigger Begins

Case Trigger Begins - Case ID #1, this accounts for 3 SOQL queries

Case Trigger Ends

Email Message Trigger Ends

Task Trigger Begins

Task Trigger Ends

Email Message Trigger Begins

Case Trigger Begins - Case ID #2, this accounts for 3 SOQL queries

Case Trigger Ends

Email Message Trigger Ends

 

And this repeats for the 5 iterations which would be 18 SOQL queries plus the original 3 putting me over the limit of 20.

 

How can I prevent this from happening?

 

Thanks,

 

Christian

 

 

 

werewolfwerewolf
Are you using On Demand Email To Case or the Email To Case Java client?
werewolfwerewolf
Email To Case itself is spawning the task trigger as a single email can generate a case, a task, and an email all at the same time.
mtbclimbermtbclimber

Does that process create tasks one at a time or does it perform one bulk task operation for all the cases?

cpo87cpo87
I know I have On-Demand enabled but I'm not sure if anything was installed behind the firewall.  My email systems guy is out of the office right now.  Can a company have it installed both ways?
werewolfwerewolf

It is possible to have both, but only the client Email To Case would create multiple cases at once (whereas On Demand Email To Case creates the cases as it gets the emails).

 

In any case I think the issue is that you have not bulkified your update statement -- you should be keeping a list of cases to update and then do that update last and on that list.  Then Case Age In Business Hours will do its 3 queries but will calculate the age for all the cases at once.

 

Are you sure the code mtbclimber gave you is in fact the trigger that's being called here?

cpo87cpo87

Yes, the Email Message trigger that is showing up in my debug log is the code that I got from mtbclimber, or at least it has the exact same name.

 

So are you saying that my update dml in the Email Message trigger needs to be bulkified?

werewolfwerewolf
Yes, that is what I'm saying, but that is what mtbclimber's trigger code did in his Database.update() call.
cpo87cpo87
Any other ideas on how I can troubleshoot this then?  I double checked and that is my only email message trigger.  I also heard back from my IT department and they confirmed that we have Email2Case behind the firewall as well as On Demand set up.
cpo87cpo87
Am I able to tell if these script errors are preventing the cases from being committed to the DB?
mtbclimbermtbclimber

So we're investigating the possibility that the on-premise email2case functionality could be processing multiple records row-by-row rather than in bulk on our side which means you have to either alter your client processing to never send more than one at a time or wait for us to fix that on our side. As I said this is under investigation so no conclusion yet but you are free to try changing your client processing if it's not inconvenient to do so.

 

When an exception occurs in an apex request that is unhandled there is no commit so cases are not being created when this happens.

cpo87cpo87
Okay, please keep me informed.  Thanks for looking into this!
Message Edited by cpo87 on 03-08-2010 07:44 AM
werewolfwerewolf

In fact it does appear that the old Email To Case client, which you are evidently using here, processes emails row-by-row.  Any chance you could migrate that email address to On Demand Email To Case?  On Demand Email To Case would not have this issue.

 

The alternative is to rewrite Case Age In Business Hours to work given the fact that multiple cases could be expected in a single transaction.  Such a thing is possible actually, and it's something I've been meaning to do for a while, although I can't guarantee I'll have time for it immediately (I am the guy who wrote it in the first place).  I'm surprised no one else has reported this issue before, but it could be because not that many people still use the old Email To Case client.

cpo87cpo87

Thanks for getting back to me on this.  We changed to On-Demand Email-to-Case but quickly reverted because this meant that we were no longer able to filter out SPAM and Out of Office email which is something we do on our exchange server, unless you know of a way to do this even though this email traffic is no longer getting as deep as our exchange server.

 

We are attempting to drop the amount of time that the interval for standard Email-to-Case pushes email into Saleforce.  Do you think this could help limit the problem since less cases would be created at one time?

werewolfwerewolf

You should be redirecting emails post-spam and post-filters (like OOO) to On Demand Email To Case, and as such it should work just like your Email To Case client.  Basically let the traffic go to the Exchange server but redirect it from there.  That would probably be the best choice.

 

You can up the refresh rate on client Email To Case but the minimum is 1 minute I think, and it's not guaranteed to solve the problem.  Incidentally I'd expect that your cases are still created just fine even when the issue does occur, just that for those cases the Case Age In Business Hours fields will be empty.