3
\$\begingroup\$

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;
}
\$\endgroup\$
5
  • \$\begingroup\$ Can we see the code of FileUtils.writeStringToFile()? From the looks of it, that will need to open the file again and again. Without profiling though, it's just a guess whether that is your current bottleneck. \$\endgroup\$ – bowmore Jul 17 '14 at 17:51
  • \$\begingroup\$ That's part of the Apache Commons FileUtils: commons.apache.org/proper/commons-io/apidocs/org/apache/commons/…). You may be onto something with that opening the file repeatedly though. Do you think that the traditional Java writer class with a bufferedwriter might be faster? \$\endgroup\$ – BarFooBar Jul 17 '14 at 17:54
  • \$\begingroup\$ Then it indeed does mean opening the file again for each line. \$\endgroup\$ – bowmore Jul 17 '14 at 18:00
  • \$\begingroup\$ Have you run a profiler to see where most of the time is being spent? \$\endgroup\$ – Ryan Jul 17 '14 at 18:06
  • \$\begingroup\$ Most of the time is being spent in the for loop ending with FileUtils.writeStringToFile(). \$\endgroup\$ – BarFooBar Jul 17 '14 at 18:09
1
\$\begingroup\$

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.

\$\endgroup\$
1
  • \$\begingroup\$ That (in conjunction with a BufferedWriter) worked well, thanks! \$\endgroup\$ – BarFooBar Jul 26 '14 at 2:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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