How to avoid OOM (Out of memory) error when retrieving all records from huge table?

Question

Hi all,

I am given a task to convert a huge table to custom XML file. I will be using Java for this job.

If I simply issue a "SELECT * FROM customer", it may return huge amount of data that eventually causing OOM. I wonder, is there a way i can process the record immediately once it become available, and remove the record from memory after that during sql retrieving process?

--- edited on 13 Jul 2009

Let me elaborate my question. I have 1 db server and 1 application server. When I issue a select query in application, the data will travel from db server to app server.

I believe (correct me if I am wrong) ResultSet will need to wait until receiving all records in the query. Even if we set fetch size as 4, for a 1000-record table, we still end up having 1000 records in heap memory of app server, is it correct? Fetch size only affect the number of round trip from/to db server.

My question is, how to start processing on that 4 (or any number) records immediately after it's arrival to app server, and dispose it to free up memory in app server?

Answer 1

With a little more information I can get a more helpful answer.

If you are using MySQL:

stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
       java.sql.ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);

from http://www.oracle.com/technology/tech/java/sqlj_jdbc/htdocs/jdbc_faq.html:

java.util.Properties info = new java.util.Properties();
info.put ("user", "scott");
info.put ("password","tiger");
info.put ("defaultRowPrefetch","15");
getConnection ("jdbc:oracle:oci:@",info);

Answer 2

I think you could use the same solution as this one. A scrollable resultset.

Answer 3

If you are using JDBC you can use a ResultSet with a cursor which you iterate through one record at a time. You need to makes sure then that you write your XML out to a file one record at a time rather than using DOM to build the XML.

Answer 4

One rule of thumb that I've learnt from my experience is that you NEVER bring ALL the data from the database to your application server. One thing you can do is implement a procedure to page your data.

You can bring one page of data containing around 1000-5000 records, process them, then again fetch the data for the next page.

Answer 5

At which stage is the OOM error occurring, is it on data retrieval or processing data to XML file?

If its data retrieval, get the data in batches. Get the total number of rows first, order the selects by the primary key and limit the rows selected to chewable sizes.

If its at creating the XML file, send the XML node of each customer to System.out.println, don't hold it in memory. Launch the program via commad line and redirect all output to a file;

java MyConverter > results.txt

As you loop through the record all is saved in the file.

Answer 6

A concept for exporting the entire table. (Note to experts: I'm aware of its shortcomings.)

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
public class FullTableExport {
    public static String toXML(String s) {
        if (s != null) {
            StringBuilder b = new StringBuilder(s.length());
            for (int i = 0, count = s.length(); i < count; i++) {
                char c = s.charAt(i);
                switch (c) {
                case '<':
                    b.append("&lt;");
                    break;
                case '>':
                    b.append("&gt;");
                    break;
                case '\'':
                    b.append("&#39;");
                    break;
                case '"':
                    b.append("&quot;");
                    break;
                case '&':
                    b.append("&amp;");
                    break;
                default:
                    b.append(c);
                }
            }
            return b.toString();
        }
        return "";
    }
    public static void main(String[] args) throws Exception {
        String table = "CUSTOMER";
        int batch = 100;

        Class.forName("oracle.jdbc.driver.OracleDriver");
        Connection conn = DriverManager.getConnection(
            "jdbc:oracle:thin:@server:orcl", "user", "pass");
        PreparedStatement pstmt = conn.prepareStatement(
            "SELECT /*+FIRST_ROWS(" + batch + ") */ * FROM " + table);
        ResultSet rs = pstmt.executeQuery();
        rs.setFetchSize(batch);
        ResultSetMetaData rsm = rs.getMetaData();
        File output = new File("result.xml");
        PrintWriter out = new PrintWriter(new BufferedWriter(
            new OutputStreamWriter(
            new FileOutputStream(output), "UTF-8")), false);
        out.printf("<?xml version='1.0' encoding='UTF-8'?>%n");
        out.printf("<table name='%s'>%n", toXML(table));
        int j = 1;
        while (rs.next()) {
            out.printf("\t<row id='%d'>%n", j++);
            for (int i = 1; i <= rsm.getColumnCount(); i++) {
                out.printf("\t\t<col name='%s'>%s</col>%n", 
                    toXML(rsm.getColumnName(i)), 
                    toXML(rs.getString(i)));
            }
            out.printf("\t</row>%n");
        }
        out.printf("</table>%n", table);
        out.flush();
    }
}

Edit The shortcomings (thanks @J.S.):

• No external libraries used beyond the ojdbc
• Nothing is closed
• A generic Exception is thrown
• It is a main method
• Usage of print for XML generation
• Oracle specific SQL
• Plain text password
• Some columns look awkward in string representation
• UTF-8 is too international
• XML structure footprint is large