0

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', '');
    }
  • What does Email.PrepareRecordStringForCSVAttachment do? Seems to me you should be passing in an List<XYZ> instead of calling it for each record. Also, is there anything running before this code is executed? Specifically Process builder? – Eric Dec 12 '16 at 2:58
  • Here is the code. 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', ''); } – Rohitha Dec 12 '16 at 3:09
0

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');
}
  • Thank you for the input Charles, but it did not help. I ran into the Heap size limit exception. I am guessing it is because of maintaining one very large string and another string holding 200 records. – Rohitha Dec 12 '16 at 3:44
  • I was going to come up with something involving String.join too but it looks like Eric managed to first. Ultimately you're generating an attachment for an e-mail, right? So is this something you're OK with doing asynchronously? If so, then you can set it up as a Batchable. Your start method would get a query locator and your execute method would process 200 records at a time. You could have an instance variable that accumulates the large string result. Then the finish method would read the result, produce the file and do whatever e-mailing you need. – Charles T Dec 12 '16 at 5:02
  • Added a String.join example anyway. :) – Charles T Dec 12 '16 at 5:24
  • Thank you Charles, that String.Join outside the loop did the trick. However, i think i will still make this asynchronous, so that i have more relaxed limits. – Rohitha Dec 12 '16 at 20:32
  • Glad to hear it did the trick - I'll have to keep that in mind myself for future projects! And yes, doing it as a Batchable or other async process will help avoid the limits even when there are more records. You would just have to change the control flow around - instead of calling this to get a return value for an E-mail, you'd pass all necessary parameters to the Batchable class constructor and then have the finish method call a method that finishes the post-actions. – Charles T Dec 12 '16 at 22:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.