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

Sorry, correction:

the huge table is about 30 million rows.

Jet Mah wrote:
> 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 Sat Jun 03 2006 - 08:06:05 CDT