Create CSV file from SObject records - How to Optimize Script Statments [duplicate]

Question

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;   

Answer 1

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.