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

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.

Hi Jet,

My guess is you meant something larger than 3000 rows? 3000 rows is not very large.

I am surprised this code even compiles, as you have qstmt defined twice. Also, there are issues (ORA-01446) with most Oracle JDBC drivers when updating a ResultSet. Is "member" a view with an INSTEAD OF trigger?

Also, it looks like you always select the first 1000 rows, so may want to just embed that in the sql string.

Lastly, can you run the sql itself in SQL*PLUS after setting autotrace on? This would enable you to get a query plan to see why it is slow. If this doesn't sound familiar, talk to your DBA, or read up on it at tahiti.oracle.com.

Regards,

Steve Received on Sat Jun 03 2006 - 07:15:01 CDT