Re: How to get the data from a huge table efficiently?
- From: "kuassi.mensah@xxxxxxxxx" <kuassi.mensah@xxxxxxxxx>
- Date: 4 Jun 2006 18:34:25 -0700
As others have mentionned, why do you want to retrieve/ship millions
rows on the client-side, just process the data within the database, and
only display/ship the results on the client-side. See my blog
http://db360.blogspot.com/ on user-defined databasde functionality
using Java (if Oracle, DB2, Sybase, Postgres), PL/SQL (if Oracle), or
other proprietary languages or de facto standard.
Kuassi
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:
//--------------------------------------------------------------------------
// 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.
.
- References:
- How to get the data from a huge table efficiently?
- From: Jet Mah
- How to get the data from a huge table efficiently?
- Prev by Date: Re: Error connecting Db2 from Tomcat via Db2Connect
- Next by Date: Re: To hibernate or not to hibernate?
- Previous by thread: Re: How to get the data from a huge table efficiently?
- Next by thread: hibernate - what is wrong??
- Index(es):
Relevant Pages
|
