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

Hi Jet,

It is hard to give you a solution while the question is not fully stated: what is the purpose of retrieving all that data from the database?

I see few problems with your code, so I embedded some of my comments below.

Valentin

Jet Mah wrote:
> 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) {

This is an infinite loop.

> >
> > 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 + "";

The same query is executed for each iteration, potentially the same result set being returned each time.

> > 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

What is the point of storing this data in memory? This Vector is only overhead. You can do all your processing below, using v, directly here, without having to do the intermediate step of storing data in memory twice.

> > Vector v = new Vector();
> > while (rs.next()) {
> > RequestBean rb = new RequestBean();

For each record in the 1,000 result set, a new RequestBean is created. Having in mind that the while(true){ ... } is an infinite loop, this seems to be the code responsible for cheiwng up all the available memory.

> > // 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 Sun Jun 04 2006 - 02:43:45 CDT