I have a class I am working on that will export a CSV that includes "Case Number", and "Last Outbound Customer Activity Date". There is some logic included in my Apex that determines the last activity date by checking the Task and Email Message objects. Specifically, in the email message object, it looks for email that have a contact's email address in the "ToAddress" field. This requires either a lot of queries, or a large list of "All Contact" emails.

See code below, is there a way I can make this more performant instead of doing a double for loop to check for a contact email address in the "To" field?

Thanks in advance!

public class ExportCalcLastActivityDate{
    public ExportCalcLastActivityDate() {

    public list<caselist> getextractCaseList() {
        List<caselist> extractCaseList = new List<caselist>();
        List<Case> caseList = [SELECT CaseNumber, Status, AccountId, 
                                   (SELECT Id, CreatedDate, ToAddress, FromAddress 
                                    FROM EmailMessages 
                                    where FromAddress like '%@mycompany.com' 
                                    ORDER BY CreatedDate DESC), 
                                   (SELECT CreatedDate 
                                    FROM Tasks 
                                    WHERE TaskSubType != 'Email' 
                                     AND Five9__Five9TaskType__c != null 
                                    ORDER BY CreatedDate DESC 
                                    LIMIT 1) 
                               from Case 
                               WHERE Status in ('New','In Progress', 'On Hold')];
        List<Contact> contactList = [SELECT Email FROM Contact where Email != null and Account.Type = 'Customer'];
        // for each case, determine the last outbound customer contact date. This is either the last email sent with the customer email address in the ToField or the last task created, whichever is later.
        for (Case c : caseList) {
            Datetime lastOutboundDate = null;
            // check the task created date
            if (c.Tasks.size() > 0) {
                lastOutboundDate = c.Tasks[0].CreatedDate;
            // find the last email sent with the customer email address in the ToField
            for (EmailMessage e : c.EmailMessages) {
                for (Contact con : contactList) {
                    // account for null email addresses
                    if (con.Email != null && e.ToAddress != null) {
                        if (e.ToAddress.contains(con.Email)) {
                            if (lastOutboundDate == null || e.CreatedDate > lastOutboundDate) {
                                lastOutboundDate = e.CreatedDate;
            // add the case and last outbound date to the list
            if (lastOutboundDate != null) {
                extractCaseList.add(new caselist(c.CaseNumber, c.Status, lastOutboundDate));
        // log the list 
        System.debug('extractCaseList: ' + extractCaseList);
        // generate a csv file for the list
        String csv = '';
        List<String> csvLines = new List<String>{'CaseNumber', 'Status', 'LastOutboundDate'};
        String fileRow = '';
        for (caselist cl : extractCaseList) {
            fileRow = cl.CaseNumber + ',' + cl.Status + ',' + cl.LastOutboundDate;
        csv = String.join(csvLines, '\r');        
        // send the csv file as an attachment to the user
        Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
        mail.setPlainTextBody('Please find the attached file.');
        mail.setSubject('Case List');
        Blob blob1 = Blob.valueOf(csv);
        Messaging.EmailFileAttachment attachment = new Messaging.EmailFileAttachment();
        mail.setFileAttachments(new Messaging.EmailFileAttachment[]{attachment});
        Messaging.sendEmail(new Messaging.SingleEmailMessage[]{mail});
        return extractCaseList;

    public class caselist {
        public String casenumber {get;set;}
        public String status {get;set;}
        public Datetime lastoutbounddate {get;set;}

        public caselist(String casenumber, String status, Datetime lastoutbounddate) {
            this.casenumber = casenumber;
            this.status = status;
            this.lastoutbounddate = lastoutbounddate;
  • Why are you checking contains instead of equality on email addresses? Seems unusual.
    – Adrian Larson
    Commented Sep 29, 2022 at 14:58
  • @AdrianLarson I'm using contains as the ToAddress field can contain multiple values separated by ';'. For example, '[email protected];[email protected]' etc..
    – guffelman
    Commented Sep 29, 2022 at 15:48

2 Answers 2


Here are 2 suggestions for making this more efficient.

  1. in case your check is basically 'any email that went to the Case's Contact' then you could create a new formula field on the Email Message object that indicates whether it is an email to the Case's Contact. I.e. a checkbox formula field with the formula ToAddress = Parent.Contact.Email Then adapt your subquery to filter only emails where the checkbox is true. This means you don't even have to query the Contacts table which will make your code much more efficient.

  2. in case your check needs to be 'any email that went to any Contact in the database' then one thing you can do to prevent the nested for loop is to only do 1 loop through contactList and put all email addresses in a Set<String> ContactEmailAddresses. Then when doing the loop through caseList you can just do if (ContactEmailAddresses.contains(e.ToAddress))


I'd also caution you against negation operators in queries. Soql, like virtually every other RDMS I can think of, resorts to a full table scan when it encounters negation (<>, !=) in the query. So you're doing a full table scan on Task when you say TaskSubType != 'email'

Also note that this tool: https://help.salesforce.com/s/articleView?id=000334796&type=1 can really help you tune your queries.

  • I wasn't expecting to see you back on here!
    – Matt Lacey
    Commented Sep 29, 2022 at 16:06
  • Just to be clear, I've never met an org that used Task, that didn't abuse Task. Which is to say, I suspect a full table scan of Task HURTS.
    – Kevin P
    Commented Sep 29, 2022 at 16:34
  • @KevinP, Thank you so much for the information!!!! I wasn't aware of this. It will surely come in handy! I know what task subtypes I'm looking for, so I'll switch that.
    – guffelman
    Commented Sep 29, 2022 at 16:54
  • @guffelman - this one might be harder: AND Five9__Five9TaskType__c != null depending on how tightly controlled your task types are. This is the kind of thing I use Custom Metadata for. Build a helper lib to pull the custom metadata records (without soql) of what types to include, and build your query dynamically.
    – Kevin P
    Commented Sep 29, 2022 at 17:05
  • This field is a custom field installed by our CTI adapter. It basically records a task for every SMS or Call that is completed related to an object. This field has values stored in it like 'Call', or 'SMS'. Should be fairly easy... AND Five9__Five9TaskType__c in ('Call, 'SMS'), right?
    – guffelman
    Commented Sep 29, 2022 at 17:25

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .