How to get the data from a huge table efficiently?

From: Jet Mah <jetmah_at_gmail.com>
Date: 2 Jun 2006 17:40:28 -0700
Message-ID: <1149295228.516857.192140@f6g2000cwb.googlegroups.com>

Hi,
I want to get the data one by one from a huge table(about 3 thousand rows) efficiently, this is my way as below:

//--------------------------------------------------------------------------

int queryEachTime = 1000;
int queryFrequency = 5000;

Connection conn = null;
try {

    while(true) {

	if(conn == null){
	    // "dbPool" is a connection pool
	    conn = dbPool.getConnection();


	}


	CallableStatement qstmt = conn.prepareCall(sql,
ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
	String sql = "SELECT ROWNUM, id, name, read " +
		"FROM member " +
		"WHERE age > 20 AND ROWNUM<=" + queryEachTime + "";
	Statement qstmt =

conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

	ResultSet rs = qstmt.executeQuery(sql);
	// If have new data
	boolean haveData = false;
	// put the data into memory
	Vector v = new Vector();
	while (rs.next()) {
	    RequestBean rb = new RequestBean();
	    // get data
	    rb.setId(rs.getBigDecimal("id"));
	    rb.setName(rs.getBoolean("name"));

	    // 放到内存中
	    v.addElement(rb);

	    // update row
	    rs.updateInt("read_stat", 1);
	    rs.updateRow();

	    haveData = true;


	}

	// close
	rs.close();
	rs = null;
	qstmt.close();
	qstmt = null;
	// release connection
	conn.close();
	conn = null;

	for(int i = 0; i < v.size(); i ++){
	    RequestBean rb = (RequestBean)v.get(i);
	    // do something


	}

	v.clear();

	/* Have finished! */
	if(!haveData) {
	    if(LOG.isDebugEnabled())
		LOG.debug("Finished!");


	}


	/* Frequency */
	try {
	    Thread.sleep(queryFrequency);


	} catch (InterruptedException e) {

	}

    }
} catch (ProxoolException e) {

    LOG.error("",e);
} catch (SQLException e) {

    LOG.error("",e);
} finally {

    if (conn != null) {

	try {
	    conn.close();


	} catch (SQLException e) {

	}

	conn = null;

    }
}

//--------------------------------------------------------------------------

But when the application run for a while, It will get the OutOfMemoryException. Any ideas? Thanks. Received on Fri Jun 02 2006 - 19:40:28 CDT