How to get the data from a huge table efficiently?


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:

//--------------------------------------------------------------------------
// SQL Server 2000 Database

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);
//qstmt.setMaxRows(queryEachTime);
//qstmt.setFetchSize(queryEachTime);
String sql = "SELECT TOP " + queryEachTime + " id, name, read " +
"FROM member " +
"WHERE age > 20";
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.

.

Relevant Pages