0

Webapp to provide csv report for user to download. I'm having issue when there is millions of rows in table to write it to the response.

I tried to select by batch(using rowNum), since putting too much of object into arraylist will lead to out of memory issue. But still, setting too much data into stringbuilder will hit out of memory issue as well.

Here is the code use to downloadCSV:

    public void downloadCSV(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

    long totalRecordCnt = getTotalRecords(batchId, ...);
    int range = 10000;


    BufferedOutputStream bos = null;
    try {
        StringBuilder tableStr = new StringBuilder();
        bos = new BufferedOutputStream(response.getOutputStream());
        tableStr.append('\ufeff');
        tableStr.append("\"" + ....);

        tableStr.append("Batch ID" + ","
                +"User Name" + "," + "Acc balance" + "\n");

        // split the records into smaller batch to process
        if (totalRecordCnt > 0) {

            long totalBatchCnt = totalRecordCnt / range;
            if (totalRecordCnt % range > 0) {
                totalBatchCnt += 1;
            }

            long totalRecordCntTemp = totalRecordCnt;
            for (int i = 1; i <= totalBatchCnt; i++) {
                long endRange = 0;
                long startRange = ((i - 1) * range) + 1L;
                if (totalRecordCntTemp < range) {
                    endRange = totalRecordCnt;
                } else {
                    endRange = startRange + range - 1;
                }

                totalRecordCntTemp = totalRecordCntTemp - range;

                // process records
                List<Account> account = null;

                account = getAccountList(batchId, startRange,endRange);


                if (account != null && !account.isEmpty()) {
                    for (Account acc : account) {

                        tableStr.append("\"" + acc.getBatchId + "\",\"" + accnt.getName
                                + "\"" + "\n");

                    }
                }
            }
        }

        // setting some response headers
        response.setHeader("Expires", "0");
        response.setHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0");
        response.setHeader("Pragma", "public");
        // setting the content type
        String csvFileName = "Acc_report" + ".csv";
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-disposition", "attachment; filename=" + csvFileName);

         bos.write(tableStr.toString().getBytes());// Write individual
        // field
                                                  // followed
        bos.flush();
    } catch (Exception e) {
        throw new IOException(e.getMessage());
    } finally {
        if (bos != null) {
            try {
                bos.close();
            } catch (Exception e) {
            }
        }
    }

}

Expected: To be able to generate report contains millions of rows instantly once using click on download button.

user11703796
  • 1
  • Nothing with "millions of rows" is instant. You'll need to chunk both the results and the writing. – Dave Newton Jun 26 '19 at 14:58
  • You might be able to stream it. IE write the row, flush the output and reset the stringbuilder for each item in your resultset. Close the output when you get to the end. See hints here: https://stackoverflow.com/questions/21378773/create-and-download-csv-file-javaservlet and http://knes1.github.io/blog/2015/2015-10-19-streaming-mysql-results-using-java8-streams-and-spring-data.html – Moob Jun 26 '19 at 15:01
  • @Dave Newton ,how did you split the writing part? as eventually it will prompt user with the Save dialog. i tried to write after each batch 10k, it will straight away output all the rows on user browser. – user11703796 Jun 26 '19 at 15:09
  • @Moob, i do it the way you mentioned, but it just straight away output on the browser.This is not the way i want it.and it took quite long to process batch by batch – user11703796 Jun 26 '19 at 15:11
  • It's "millions of rows" -- how fast do you think it will be? Normally when creating giant files you return immediately and notify the user when the job is complete, e.g., a pop-up message, an email, etc. It simply cannot be instantaneous when dealing with huge amounts of data! – Dave Newton Jun 26 '19 at 15:15
  • _putting too much of object into arraylist will lead to out of memory issue_ Look at the source code for class `java.util.ArrayList` to understand how it works, then consider using the constructor for that class that takes an "initial capacity" parameter. _setting too much data into stringbuilder will hit out of memory issue as well_ Consider `StringBuilder` constructor that takes "initial capacity" parameter. – Abra Jun 26 '19 at 16:34
  • What database are you using? If its MySQL or MariaDB (and maybe others) you could generate the CSV as an [outfile](https://dev.mysql.com/doc/refman/8.0/en/select-into.html) (which would save it to the server) and then serve that file. See:https://stackoverflow.com/a/356605/1921385 – Moob Jun 26 '19 at 17:09

0 Answers0