Apex CPU time limit exceeded when processing large number of records in a for loop

Question

I ran into the CPU time limit exceeded error when processing the results of a SOQL query in a for loop. I tried several alternatives and nothing seems to help. Any help is greatly appreciated.

Here is my code.

private Messaging.EmailFileAttachment getLocationsAttachment(){
        //Construct the table header in CSV format.
        string finalstr = Email.PrepareRecordStringForCSVAttachment(new string[] {'NAME','ID','ACCOUNT__R.NAME','ACCOUNT__C','STREET_AADDRESS__C','CITY__C','STATE__C','ZIP__C'});
        for(Location__C l: [Select Name, Id, Account__r.Name, Account__c, Street_Address__c, City__c, State__c, ZIP__c FROM Location__c])
        {
            //Construct each row of the SOQL result set in CSV format.
            string recordstr = Email.PrepareRecordStringForCSVAttachment(new object[]{l.Name, l.Id, l.Account__r.Name, l.Account__c, l.Street_Address__c, l.City__c, l.State__c, l.ZIP__c});                  
            finalstr += recordstr;
        }
        return Email.ConvertToEmailAttachment(finalstr, 'Locations.csv');
    }

public static Messaging.EmailFileAttachment ConvertToEmailAttachment(string attachmentContents, string attachmentName){
        Messaging.EmailFileAttachment attachment = new Messaging.EmailFileAttachment();
        blob csvBlob = Blob.valueOf(attachmentContents);
        attachment.setFileName(attachmentName);
        attachment.setBody(csvBlob);
        return attachment;
    }

The number of records returned by the soql is 26,716 (Not too many).

I tried got rid of the "ConvertToEmailAttachment" function and made the code inline thinking it is probably spending too much time updating the stack during the function calls. However that did not change anything.

I also tried to use the for loop syntax to process batches of records from SOQL, that did not help either. Here is the code.

private Messaging.EmailFileAttachment getLocationsAttachment(){
    //Construct the table header in CSV format.
    string finalstr = Email.PrepareRecordStringForCSVAttachment(new string[] {'NAME','ID','ACCOUNT__R.NAME','ACCOUNT__C','STREET_AADDRESS__C','CITY__C','STATE__C','ZIP__C'});
    for(List<Location__C> locs: [Select Name, Id, Account__r.Name, Account__c, Street_Address__c, City__c, State__c, ZIP__c FROM Location__c])
    {
        for(Location__C l: locs)
        {
        //Construct each row of the SOQL result set in CSV format.
        string recordstr = Email.PrepareRecordStringForCSVAttachment(new object[]{l.Name, l.Id, l.Account__r.Name, l.Account__c, l.Street_Address__c, l.City__c, l.State__c, l.ZIP__c});
        finalstr += recordstr;
        }
    }
    return Email.ConvertToEmailAttachment(finalstr, 'Locations.csv');
}

My apologies, i thought i provided the code for generating the string. Here it is.

public static string PrepareRecordStringForCSVAttachment(object[] columnValues){
        string recordString = '"';
        integer colValsCount = columnValues.size();
        for(integer i = 0; i<colValsCount - 1; i++)
        {
            recordString += columnValues[i] + '","';
        }
        recordString += columnValues[colValsCount - 1] + '"\r\n';
        return recordString.replace('null', '');
    }

Answer 1

I'm wondering if maybe the way Apex processes concatenations is suboptimal. If you're adding one line at a time to a very long string it may be doing a lot of unnecessary work.

Here's something to try... In your second code block, inside the first for loop, initialize a temporary string and make that the one you append to in the inner loop. Then after the inner loop, add the temp string to the final. So now you'd have:

private Messaging.EmailFileAttachment getLocationsAttachment(){
    //Construct the table header in CSV format.
    string finalstr = Email.PrepareRecordStringForCSVAttachment(new string[] {'NAME','ID','ACCOUNT__R.NAME','ACCOUNT__C','STREET_AADDRESS__C','CITY__C','STATE__C','ZIP__C'});
    for(List<Location__C> locs: [Select Name, Id, Account__r.Name, Account__c, Street_Address__c, City__c, State__c, ZIP__c FROM Location__c])
    {
        String semifinal = '';
        for(Location__C l: locs)
        {
            //Construct each row of the SOQL result set in CSV format.
            string recordstr = Email.PrepareRecordStringForCSVAttachment(new object[]{l.Name, l.Id, l.Account__r.Name, l.Account__c, l.Street_Address__c, l.City__c, l.State__c, l.ZIP__c});
            semifinal += recordstr;
        }
        finalstr += semifinal;
    }
    return Email.ConvertToEmailAttachment(finalstr, 'Locations.csv');
}

If that doesn't work either, then you might have to consider using a Batchable class instead.

EDIT: For what it's worth, if we use String.join, this is something like how I'd do it (not doing a String.join in the inner loop as that appears to actually take more CPU time to do repeatedly):

private Messaging.EmailFileAttachment getLocationsAttachment(){
    //Construct the table header in CSV format.
    string header = Email.PrepareRecordStringForCSVAttachment(new string[] {'NAME','ID','ACCOUNT__R.NAME','ACCOUNT__C','STREET_AADDRESS__C','CITY__C','STATE__C','ZIP__C'});
    string[] lines = new string[]{header};

    for(List<Location__C> locs: [Select Name, Id, Account__r.Name, Account__c, Street_Address__c, City__c, State__c, ZIP__c FROM Location__c])
    {
        for(Location__C l: locs)
        {
            //Construct each row of the SOQL result set in CSV format.
            string recordstr = Email.PrepareRecordStringForCSVAttachment(new object[]{l.Name, l.Id, l.Account__r.Name, l.Account__c, l.Street_Address__c, l.City__c, l.State__c, l.ZIP__c});
            lines.add(recordstr);
        }
    }
    return Email.ConvertToEmailAttachment(String.join(lines,''), 'Locations.csv');
}