Take the 2-minute tour ×
Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It's 100% free, no registration required.

This question already has an answer here:

I have following code which Create CSV file from any SOQL query, but the problem is its not very efficient (not optimized to execute with less number of statements). This code runs out of script statements for 20,000 records in batch mode.

I think for loop within for loop is the problem. is there any way to optimize it?

Please note that SFDC reports are not going to solve this problem. Thanks.

List<SObject> queryResults = Database.query('select id,name,billingcity from account'); any query here
String tempQuery = reportObj.SOQL_Query__c.subString(0,reportObj.SOQL_Query__c.indexOfIgnoreCase('From'));
List<String> queryFields = list of fields used in query exmaple - {'id','name','billingcity'}       ;

String csvFileString = ''; 
for(String fieldName : queryFields)
{
    csvFileString = csvFileString + ',' + fieldName.replace(',','');
} 
csvFileString = csvFileString.replaceFirst(',','') + '\n';

String fileRow = '';
String fieldValue = '';
for(SObject obj : queryResults) **//THIS FOR LOOP IS CONSUMING MORE SCIRPT STATMENTS, IS THERE ANY WAY TO OPTIMIZE THIS ?**
{
    fileRow = '';
    System.debug('===>' + obj); 

    for(String fieldName : queryFields)
    {
        fieldValue = '' + obj.get(fieldName);
        fieldValue = fieldValue.replace(',','');
        fileRow = fileRow + ',' + fieldValue;
    }
    fileRow = fileRow.replaceFirst(',','');
    csvFileString = csvFileString + fileRow + '\n';
    //SObject a = c.getSObject('Account');// for parent objects fields like Account.Name
}
Blob csvBlob = Blob.valueOf(csvFileString);
Attachment AttachmentObj = new Attachment(Body = csvBlob,
                              Name = 'Test-' +System.now().format('yyyy-MMM-dd-hh-mm-ss') + '.csv',
                              parentId = 'some id');
insert AttachmentObj;   
share|improve this question

marked as duplicate by Greg Grinberg, Daniel Blackhall, Mike Chale, Saariko Feb 21 '13 at 11:07

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

    
How many rows of data do you need to run through? –  Wes Nolte Feb 17 '13 at 21:30

1 Answer 1

up vote 5 down vote accepted

Because your code is completely dynamic you need to use the loop and nested loop resulting in N x M executions (N = no. of rows, M = no. of fields) of your inner loop code and N executions of the outer loop code.

I don't think that there's much efficiency to be gained, but a lot of your code is geared toward removing commas. Why don't you use a different delimiter while you build the csvFileString e.g. '+' and then remove all the commas at the end, and then replace the '+' with a ','?

csvFieldString.replaceAll(',', '').replaceAll('+',',');

No matter what you do though you're going to hit a limit at some point.

share|improve this answer
1  
Thanks a lot for your reply, it helped me to reduce 50% of the script statements. –  PK85 Feb 18 '13 at 18:22

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