renderAs CSV (10K+ rows): What are the best practices available to avoid hitting Apex cpu time limit exceeded limits?

Question

I am trying to download a CSV using render as attribute in apex:page. The output data that is rendered is the real-time response from a callout from salesforce to SAP webservice. The number of records that is spitting out is roughly 14k. As per the previous posts in stackexchange i optimized the code by,

a. declaring several variables to transient

b. reduced the looping situations

c. nullified the objects and variables after their usage is completed

d. reduced utility function calls thinking that is contributing to cpu time

Now my questions are,

1. I tried out several options to avoid this exception, but still i am getting this. What should i do?
2. Is there any other option to achieve my goal i.e., to download the csv in real time?
3. If real time csv download not going to work, then what would be next best alternate option? How it could be achieved?
4. How to minimize CPU time and heap size?

Debug Log:

16:10:45.068|LIMIT_USAGE_FOR_NS|(default)|
  Number of SOQL queries: 0 out of 100
  Number of query rows: 0 out of 1000000
  Number of SOSL queries: 0 out of 20
  Number of DML statements: 0 out of 0
  Number of DML rows: 0 out of 0
  Maximum CPU time: 13241 out of 10000 ******* CLOSE TO LIMIT
  Maximum heap size: 3912936 out of 6000000 ******* CLOSE TO LIMIT
  Number of callouts: 1 out of 10
  Number of Email Invocations: 0 out of 0
  Number of future calls: 0 out of 10
  Number of Mobile Apex push calls: 0 out of 10

The above debug log said Maximum CPU time 13241 as "CLOSE TO LIMIT" but on seeing it i am thinking it exceeded the limit. Is that possible and salesforce system allows to exceed the CPU time? My code is given below,

Method to achieve this.

global static String[] TestOpenInvoicesCSV(String CustNo) {
    lstReqDataJS = new List<string> {};
    transient String[] AllCSVData = new String[] {};
    try {
            transient DMsapComDocumentSapSoapFunctionsMcS.Z_GSSMWFM_HNDL_EVNTRQST00_Binding sap1 = new DMsapComDocumentSapSoapFunctionsMcS.Z_GSSMWFM_HNDL_EVNTRQST00_Binding();       
            transient List<List<String>> lstResp = new List<List<String>> {};
            sap1.timeout_x = 120000;
            transient DMsapComDocumentSapSoapFunctionsMcS.ZgssmbstDatarcrd01 req = new DMsapComDocumentSapSoapFunctionsMcS.ZgssmbstDatarcrd01();      


            lstInvoicesJS = new List<Invoice> {};

            //DATA-TYPE[.]ZGSEVDST_OPENINV[.]RESPONSE-TYPE=FULL-SETS;ROW-COUNT=9;[.]VBELN[.]FKDAT[.]KUNRG[.]NAME[.]NETWR[.]WAERK[.]DUEDATE
            //ZGSEVDST_OPENINV[.]90020574[.]2000-06-14[.]3050[.]Thomas Bush Inc.[.]7602.00[.]USD[.]2000-07-14 

            //populating item values

            transient String EventType = 'EVENT[.]SFDC-OPEN-INV-GET[.]VERSION[.]0[.]RESPONSE-TYPE[.]FULL-SETS';
            transient String InputFields = '';
            transient String InputData = 'ZGSEST_CSTMRSRCH01[.]'+CustNo;
            lstReqDataJS.add(EventType);
            lstReqDataJS.add(InputFields);
            lstReqDataJS.add(InputData);
            req.item = Utility.LoadRequestData(lstReqDataJS);
            sap1.timeout_x = 120000;
            transient DMsapComDocumentSapSoapFunctionsMcS.ZGssmwfmHndlEvntrqst00Response_element ret =  sap1.ZGssmwfmHndlEvntrqst00('',req);

            If(ret.DpostMssg.item != null) {
                transient DMsapComDocumentSapSoapFunctionsMcS.Bapiret2 bp2 = ret.DpostMssg.item[0];

                ApexPages.Message myMsg = new ApexPages.Message(ApexPages.Severity.FATAL,bp2.Message);
                ApexPages.addMessage(myMsg);
            }

            lstResp = Utility.ParseMultipleResponse(ret);
            ret = null;
            //System.debug('lstResp:>>>>>>>>>>>>>>'+lstResp.size());
            //return lstInvoices;
            if(lstResp.size()>0) {
                //System.debug(lstResp);
                transient Integer cnt = 0;
                transient Decimal amt = 0.0;
                transient Map<String, Integer> mapFieldPosition = new Map<String, Integer> {};
                //mapFieldPosition = Utility.getOpenInvFieldPositionMap();

                transient String CSVHeader = '';
                transient String CSVData = '';

                CSVHeader = 'Invoice Number,Invoice Date,Payer,Payer Name,Amount,Currency,Payment Due Date,Overdue Days\n';
                for(List<String> l: lstResp) {
                    CSVData += l[1] +','+l[2]+','+l[3]+','+l[4]+','+Decimal.valueOf(l[5])+','+l[6]+','+l[7]+','+l[8]+'\n';
                    cnt++;
                    if(cnt >100000) {
                        AllCSVData.add(CSVHeader+CSVData);
                        return AllCSVData;
                    }
                    l = null;
                }
                AllCSVData.add(CSVHeader+CSVData);
                CSVHeader = null;
                CSVData = null;
                return AllCSVData;
            }
    }catch(Exception e){
        Apexpages.Message msg = new Apexpages.Message(Apexpages.Severity.FATAL,'Oops, please try again!!');
        Apexpages.addMessage(msg);
        System.debug('Invoice Exception:'+e);
    }   
    return AllCSVData;

}      

class to create the csv output:

public with sharing class OICSVBuilder extends CSVBuilder {

    public override String buildCSV(Object[] CSV) {
        transient String[] strCSV = (String[])csv;
        return strCSV[0];
    }
}

Visualforce page that exports the csv:

<apex:page StandardController="Account" extensions="OpenDocumentsController" showHeader="false" sidebar="false" standardStylesheets="false" contentType="application/vnd.ms-excel#{!CSVTab}_{!CustNo}.csv" cache="true">{!output}</apex:page>

Answer 1

So, if you'll permit me a bit of an unorthodox approach:

Utilize the Analytics API to async run a report within your visualforce page, then render it as csv. Use the Report as your query and data collection tool, with Visualforce / js acting as your data manipulation before rendering.