Improving performance in generating an Excel file

Question

I am using this code to write more than 170,000 rows to an Excel document. All in all, the file generation process takes about 3 minutes total. (I've already shaved off about ten minutes by shaving off overhead in the form of Apache POI and an unnecessary ArrayList) I'd like to shave off some more time, but I'm currently out of ideas.

public File generateDelimitedFile(String sql, String outputFilename, String delimiter, String bodyStringDelim) throws SQLException, IOException{
    File output = new File(outputFilename);

    try (

        Connection connection = DriverManager.getConnection(connectionUrl);
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(sql)
    ) {

        resultSet.setFetchSize(excelFetchSize);
        ResultSetMetaData meta = resultSet.getMetaData();
        int columnCount = meta.getColumnCount();

        StringBuilder headerBuilder = new StringBuilder();
        for (int i = 1; i < columnCount + 1; i++ ) {
            String name = meta.getColumnName(i);
            headerBuilder.append(name);
            if (i != columnCount) {
                headerBuilder.append(delimiter);
            }
        }
        FileUtils.writeStringToFile(output, headerBuilder.toString(), true);

        while(resultSet.next()) {
            StringBuilder lineBuilder = new StringBuilder();

            for (int column = 1; column <= columnCount; column++) {
                String value = resultSet.getString(column);
                if (value != null) {
                    if(bodyStringDelim != null){
                        lineBuilder.append(bodyStringDelim);
                    }
                    lineBuilder.append(value);
                    if(bodyStringDelim != null){
                        lineBuilder.append(bodyStringDelim);
                    }
                }

                if (column != columnCount) {
                    lineBuilder.append(delimiter);
                }
            }

            FileUtils.writeStringToFile(output, lineBuilder.toString(), true);
        }
        logger.info("Writing data to file");

    } catch (SQLException ex) {
        logger.error("Error generating file from SQL", ex);
        throw ex;
    } catch (IOException ex) {
        logger.error("Error writing file", ex);
        throw ex;
    }

    return output;
}

Answer 1

I'd like to shave off some more time, but I'm currently out of ideas.

Run the code in a profiler, to find out where the bottleneck is. At a minimum, force the jvm to dump the thread stacks every 5 seconds or so, and find out where it is (because it is probably in the slow place almost all the time).

In your main loop, there are really two different ideas. First idea: take the current result, and parse it to a string; second idea: to write that string out to a file. You should make the separation of those ideas explicit.

That will be a lot easier if you create objects to encapsulate those responsibilities.

class Parser {
    private final String bodyStringDelim;
    private final String delimiter;
    private final int columnCount;

    Parser(String bodyStringDelim, delimiter, columnCount) {
        ...
    }

    String parser(ResultSet resultSet) {
        StringBuilder lineBuilder = new StringBuilder();

        for (int column = 1; column <= columnCount; column++) {
            String value = resultSet.getString(column);
            if (value != null) {
                if(bodyStringDelim != null){
                    lineBuilder.append(bodyStringDelim);
                }
                lineBuilder.append(value);
                if(bodyStringDelim != null){
                    lineBuilder.append(bodyStringDelim);
                }
            }

            if (column != columnCount) {
                lineBuilder.append(delimiter);
            }
        }

    }
}

If the garbage collector is getting in your way, you might try reusing the StringBuilder to see if that helps

class Parser {
    private final StringBuilder lineBuilder = new StringBuilder();

    private final String bodyStringDelim;
    private final String delimiter;
    private final int columnCount;

    Parser(String bodyStringDelim, delimiter, columnCount) {
        ...
    }

    String parser(ResultSet resultSet) {
        lineBuilder.setLength(0);
        ...

Similarly, you have a writer class -- at the moment, this is a lot simpler.

class Writer {
    private final File output;

    Writer (File output) {...}

    void write(String line) {
        FileUtils.writeStringToFile(output, line, true);
    }
}

FileUtils.writeStringToFile is the wrong idea for this problem -- in addition to opening and closing the file every time, you have to keep seeking forward in the file (which is getting longer) and you are flushing each individual line of the file to disk.

Much better here to manage your own FileOutputStream, which is opened with the append flag set. You can still use IOUtils.write() if you like.

If that's still not fast enough an alternative would be to move the File IO to a different thread. In that case, you would have two "writer" implementations. The one running here, it would actually be pushing the Strings into a concurrent data structure (ConcurrentLinkedQueue, Disruptor, etc), and then the "real" writer lives in a second thread, pulling batches of data out of the shared structure, buffering it, and flushing it to disk.

Theoretically, this would allow you to overlap the slow parts of the read with the slow parts of the write, trading away CPU cycles for better wall clock time.