Best way to handle huge write operation

[Anil Vupputuri]

Hi,
I've around two million records in database and need to put it in a flat file. What is the best way.
I've tried BufferedWriter but ended up getting java.lang.OutOfMemory exception though I use 'out.flush()' for every record.
And also I invoke java with maximum heap size like,
'java -Xms1024m -Xmx1024m'

Any suggestions to handle this kind of massive operation. Thanks.

[Stephen Huey]

Maybe you should post some code. Is part of your problem that you've got a humongous ResultSet object? If so, how about trying to select only a subset of the records at a time?

[Anil Vupputuri]

I can't split the result set as per requirement. Here is the code,

 
boolean b = stmt.execute("select * from big_table");
            
            BufferedWriter  out = new BufferedWriter (
             new FileWriter("ib_dyn.txt"));
            
            //Writer out = new PrintWriter(new BufferedWriter(new FileWriter("ib_dyn_10+11mnth_Java.txt")), true);
            
            ResultSet rs = null;
            if(b)
            rs = stmt.getResultSet();
            int i=0;
            StringBuffer result = new StringBuffer("");
                        
            while(rs.next()){
            
  
                result.delete(0,result.length());
                
            result.append(i);
            result.append(":");
            result.append(rs.getString(1));
            result.append("|");
            result.append(rs.getString(2));
            result.append("|");
            result.append(rs.getString(3));
            result.append("|");
            result.append(rs.getString(4));
            result.append("|");
            result.append(rs.getString(5));
            result.append("|");
            result.append(rs.getString(6));
            result.append("|");
            result.append(rs.getString(7));
            result.append("|");
            result.append(rs.getString(8));
            result.append("|");
            result.append(rs.getString(9));
            result.append("|");
            result.append(rs.getString(10));
            result.append("|");
            result.append(rs.getString(11));
            result.append("|");
            result.append(rs.getString(12));
            result.append("|");
            result.append(rs.getString(13));
            result.append("|");
            result.append(rs.getString(14));
            result.append("|");
            result.append(rs.getString(15));
            result.append("|");
            result.append(rs.getString(16));
            result.append("|");
            result.append(rs.getString(17));
            result.append("|");
            result.append(rs.getString(18));
            result.append("|");
            result.append(rs.getString(19));
            
            out.write(result.toString());
                out.newLine();    
                out.flush();
            i++;
        }
      
 
            out.close();
 
 

[Joe Ess]

I don't see anything that would necessarily give you a memory leak. How big are your records?
Try leaving the StringBuffer out. Just write each element individually. You're using a BufferedWriter so you won't be losing anything on the performance side. You'll probably gain, since BufferedWriter's buffer (8k) may be smaller than your record size.

[Jim Yingst]

Try moving

StringBuffer result = new StringBuffer("");

to be inside the while(rs.next()) loop. The problem right now is that you're adding all the results onto one huge StringBuffer. Also your output is probably something like

a
ab
abc
abcd
abcde
abcdef
abcdefg

rather than

a
b
c
d
e
f
g

Removing the StringBuffer entirely as Joe suggests would also work.

[Joe Ess]

Originally posted by Jim Yingst:
The problem right now is that you're adding all the results onto one huge StringBuffer.

I don't think that's the case. This line:

  
result.delete(0,result.length());

which _is_ inside the loop should delete the contents of the StringBuffer.
It is good practice to not reuse StringBuffer instances, as deleting the contents of a StringBuffer does not resize the internal storage buffer, so a particularly large record can cause a StringBuffer instance to hog a lot of memory for the duration of it's existance.

[Jim Yingst]

Ah yes, I missed the delete(), thanks. I agree with your other points.