Re: How to get the data from a huge table efficiently?

Jet Mah wrote:
Sorry, correction:

the huge table is about 30 million rows.
....
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;
}
....
for(int i = 0; i < v.size(); i ++){
RequestBean rb = (RequestBean)v.get(i);
// do something
}
v.clear();
....
But when the application run for a while, It will get the
OutOfMemoryException. Any ideas? Thanks.

I'm not surprised. You're creating 30 million RequestBean objects and storing them in a Vector. This is your real problem. As Andy Flowers has already said, is it really necessary to store all 30 million RequestBean objects and then loop over them afterwards?

Instead, do the processing on each RequestBean object *within* the loop where you read from your database:

while (rs.next()) {
RequestBean rb = new RequestBean();
// get data
rb.setId(rs.getBigDecimal("id"));
rb.setName(rs.getBoolean("name"));

// Do not store the RequestBean in a Vector!
// Do the processing on the RequestBean object
// *** HERE ***

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

haveData = true;
}

You could also save memory and time if you can re-use the RequestBean object:

RequestBean rb = new RequestBean();

while (rs.next()) {
// assume that RequestBean has a "clear" method which resets
// its state to make it look like a newly-created
// RequestBean
rb.clear();

// get data
rb.setId(rs.getBigDecimal("id"));
rb.setName(rs.getBoolean("name"));

// Do not store the RequestBean in a Vector!
// Do the processing on the RequestBean object
// *** HERE ***

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

haveData = true;
}

Now you only need to create *one* RequestBean object, and you re-use it.

David Harper
Cambridge, England
.