0

Taking a too long time to write the data in the CSV file in java. Here I am writing the data into the CSV file. 100,000 records it takes 1hr 10 minutes to write into a csv file. But query takes 2 minutes to fetch 100,000 record.

try {
    writer = new BufferedWriter(new FileWriter(csvFilename));
    con = DriverManager.getConnection("url", "Username", "pwd");
    stmt = con.createStatement();
    try {
        rs = stmt.executeQuery(SQL);
    } catch (SQLException er) {
        logger.info(" SQL error {}" + er);
    }

    while(rs.next()) {
        //Here splitting the data into 1000 records for each file
        String line =  Objects.toString(rs.getString(1),"") + "," + Objects.toString(rs.getString(2),"") + "," + rs.getString(3) + "," + Objects.toString(rs.getString(4),"");
        writer.write(line);
        writer.newLine();
        success.incrementAndGet();

        while(success.get()>1000) {
            writer.close();
            File f = new File("/Users/xxx/Documents/Deployments/"+filecount+"CSVTaskFileUnion.csv");
            writer = new BufferedWriter(new FileWriter(f));
            filecount.incrementAndGet();
            success.set(0);
        }
    }
}
catch (Exception e)
{
    logger.info("exception : {}" , e.getMessage());
}
finally
{
    if(rs!=null)
        rs.close();
        if(con!=null)
            con.close();
            writer.flush();
            writer.close();
        }
Abra
  • 19,142
  • 7
  • 29
  • 41
Sriram Kumar
  • 21
  • 6
  • Have you seen this ?https://stackoverflow.com/questions/1062113/fastest-way-to-write-huge-data-in-text-file-java – Naveen Kulkarni Jun 02 '22 at 09:43
  • 1000 records per file is pretty small. So you are producing 100 files. Why? Why not 10 files with 10,000 records each? Or *one* file with all 100,000 records? What exactly is the point of this? And why `while` instead of `if`? And why build up `line` in memory instead of writing each column separately? Are you sure this code works correctly? – user207421 Jun 02 '22 at 09:45
  • You are creating a lot of garbage with all those intermediate `String` that are being created. You also might want to increase the JDBC fetch size to improve performance, next why the use of an `AtomicInteger` it doesn't add anything here as the code isn't multithreaded, just use a simple counter. Finally writing files to the file system is slow (dead slow) so also make sure that nothing is preventing you from writing in full speed, like a virusscanner on your system. – M. Deinum Jun 02 '22 at 10:01
  • 2
    Did you run your program under a profiler, spotting where you really spend the majority of time? Guessing the cause of performance problems is very likely to fail. – Ralf Kleberhoff Jun 02 '22 at 10:15

0 Answers0