CODEWITHSUNDEEP
javafilecsviobufferedwriter
John Roberts
John Roberts

Reputation: 5966

Slow BufferedWriter Performance

I have a method that writes 44 MB worth of data from a ResultSet to a CSV file. However, it is taking about 3.5 minutes to complete. This seems slow for only 44 MB of data. Can anyone see anything slowing down my code?:

public static void convertToCSV(final ResultSet rs) throws SQLException, IOException {
    final BufferedWriter fw = new BufferedWriter(new FileWriter(new File("alert.csv")));
    while (rs.next()) {
        fw.write(rs.getString("FIELD1")+",");
        fw.write(rs.getString("FIELD2")+",");
        fw.write(rs.getString("FIELD3")+",");
        final String clobValue = rs.getString("FIELD4");
        if(clobValue==null)
            fw.write("null,");
        else{
            fw.write("\""+clobValue+"\",");
        }
        final Date date = new Date(rs.getLong("FIELD5"));
        final DateFormat format = new SimpleDateFormat("yyyyMMdd HH:mm:ss");
        format.setTimeZone(TimeZone.getTimeZone("Etc/UTC"));
        final String dateTime[] = format.format(date).split(" ");
        fw.write(dateTime[0]+",");
        fw.write(dateTime[1]);

        fw.write("\n");
    }
    fw.close();
}

Upvotes: 2

Views: 1963

Answers (4)

JonK
JonK

Reputation: 2108

You can increase the fetch size on the Statement that produced your ResultSet - this will reduce the amount of times that it needs to go back to the database to fetch the next batch of rows (it is set to 10 by default). The downside of this is that it will increase the memory footprint of the ResultSet because it will be holding more data in memory.

Your SimpleDatFormat object is created with the same data on each iteration of the loop - if you move it outside the loop you're only instantiating it once. You can also move the setTimeZone(...) statement outside the loop too.

String concatenation using variable values internally creates a new StringBuilder object each time you do it - you can make further gains by ensuring that you're never doing concatenation within your loop. You can either do this by creating your own StringBuilder manually, putting the whole row's worth of data into it, and then writing the row to the BufferedWriter once (at the probable expense of more memory) or you can put in more write(...) calls to avoid doing the concatenation.

Upvotes: 1

M. Deinum
M. Deinum

Reputation: 124526

Something like this could be faster 

public static void convertToCSV(final ResultSet rs) throws SQLException, IOException {
    final BufferedWriter fw = new BufferedWriter(new FileWriter(new File("alert.csv")));
    final DateFormat format = new SimpleDateFormat("'yyyyMMdd','HH:mm:ss'");
    format.setTimeZone(TimeZone.getTimeZone("Etc/UTC"));

    while (rs.next()) {
      StringBuilder sb = new StringBuilder();
      sb.append(rs.getString("FIELD1")).append(',')
        .append(rs.getString("FIELD2")).append(',')
        .append(rs.getString("FIELD3")).append(',');

        final String clobValue = rs.getString("FIELD4");
        if(clobValue==null)
          sb.append("null,");            
        else{
          sb.append('\"').append(clobValue).append('\"').append(',');
        }
        Date date = new Date(rs.getLong("FIELD5"));
        sb.append(format.format(date)).append('\n');
        fw.write(sb.toString());
    }
    fw.close();
}
  1. The creation of the DateFormat is only done once, outside the loop.
  2. Use a StringBuilder
  3. Avoid temp object creation as much as possible.

You could even reuse the StringBuilder, by moving the creation out of the loop and after writing it do a setLength(0).

Upvotes: 3

Joop Eggen
Joop Eggen

Reputation: 109547

The following should be a bit faster.

fw.write(rs.getString("FIELD1"));
fw.write(',');

Also declare and prepare the SimpleDateFormat before the loop.

final DateFormat format = new SimpleDateFormat("yyyyMMdd','HH:mm:ss");

(Single quotes not needed here.) without split which is expensive.

Upvotes: 2

wero
wero

Reputation: 32980

This may depend on the JDBC driver, your fetch strategy, the disk...

But you can certainly avoid creating so much temporary objects:

  1. Move the DateFormat out of the loop.
  2. Avoid creating temporary strings rs.getString("FIELD1")+"," and instead do two write calls on the writer.
  3. Avoid the split operation.

Upvotes: 3

Related Questions