CODEWITHSUNDEEP
javacsvresultset
Samurai
Samurai

Reputation: 843

How to improve performance of writing DB data to CSV

I have the following code to read data from oracle database and write into csv file. I used OpenCSV jar to write. It takes 230 seconds to write 1MB data. Is there any other way to increase the performance?

springJdbcTemplate.query(query,new ResultSetExtractor<ResultSet>(){

            @Override
            public ResultSet extractData(ResultSet rs) throws SQLException,
                    DataAccessException {
                try {
                    CSVWriter writer = new CSVWriter(new FileWriter("C:/csv/Sample.csv"), ';');
                    writer.writeAll(rs, true);
                } catch (Exception e) {
                    System.out.println("Exception -> "+e);
                }

                return rs;
            }});

Upvotes: 2

Views: 3394

Answers (2)

pedram bashiri
pedram bashiri

Reputation: 1386

This is the standard way of doing it, there's nothing wrong with your code; the only suggestion I'd have about your code is to use BufferedWriter or OutputStreamWriter like:

CSVWriter writer = new CSVWriter(new OutputStreamWriter(new FileOutputStream("C:/csv/Sample.csv"))

It may help just a little bit, but 230 seconds is not about writing a 1MB CSV file that's got to be about your database connection. Try just looping through the result set without writing to a file, I bet you'd get the same time. Try setting the fetchSize for your statement (Statement or PreparedStatement) like

stmt.setFetchSize(1000);

This can significantly reduce the result set fetching time.

Upvotes: 1

Peter Lawrey
Peter Lawrey

Reputation: 533930

It is taking 7 seconds without writing.

I can't image why the CSVWriter is so slow unless it need buffering.

Can you try

CSVWriter writer = new CSVWriter(
                   new BufferedWriter(new FileWriter("C:/csv/Sample.csv")), ';');

and add

writer.close();

or use Java 7+

try(CSVWriter writer = ...) {

try this

import java.io.*;

public class DumbCSVWriter {
    private final Writer writer;
    private final String sep;

    public DumbCSVWriter(Writer writer, String sep) {
        this.sep = sep;
        this.writer = writer instanceof BufferedWriter ? writer : new BufferedWriter(writer);
    }

    public void addRow(Object... values) throws IOException {
        for (int i = 0; i < values.length - 1; i++) {
            print(values[i]);
            writer.write(sep);
        }
        if (values.length > 0)
            print(values[values.length - 1]);
        writer.write("\n");
    }

    private void print(Object value) throws IOException {
        if (value == null) return;
        String str = value.toString();
        if (str.contains(sep) || str.contains("\"") || str.contains("\n")) {
            str = '"' + str.replaceAll("\"", "\"\"");
        }
        writer.write(str);
    }

    public static void main(String[] args) throws IOException {
        long start = System.nanoTime();
        File file = new File("/tmp/deleteme");
        DumbCSVWriter writer = new DumbCSVWriter(new FileWriter(file), ";");
        String[] words = "hello,0123456789,has a;semi-colon,has a \"quote".split(",");
        for (int i = 0; file.length() < 1024 * 1024; i++) {
            writer.addRow(words);
        }
        writer.close();
        long time = System.nanoTime() - start;
        System.out.printf("Time tow rite 1 MB %.3f%n", time / 1e9);
    }

    private void close() throws IOException {
        writer.close();
    }
}

prints

Time to write 1 MB 0.307

Upvotes: 2

Related Questions