Is there a way to make this apex more performant?

Question

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;
            csvLines.add(fileRow);
        }
        csv = String.join(csvLines, '\r');        
        // send the csv file as an attachment to the user
        Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
        mail.setSaveAsActivity(false);
        mail.setUseSignature(false);
        mail.setPlainTextBody('Please find the attached file.');
        mail.setSubject('Case List');
        mail.setTargetObjectId(UserInfo.getUserId());
        Blob blob1 = Blob.valueOf(csv);
        Messaging.EmailFileAttachment attachment = new Messaging.EmailFileAttachment();
        attachment.setBody(blob1);
        attachment.setFileName('CaseList.csv');
        attachment.setContentType('text/csv');
        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;
        }
    }
}

Answer 1

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))

Answer 2

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.