Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

huge recordset problem

 
antonio saginario
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
When I use
ResultSet rs = stmt.executeQuery("Select * from ..."); I retrieve a ResultSet which contains many records (say 10000). Subsequently I loop on the ResultSet and load a Vector with data extract from ResultSet. In particular i nested two loop like in example below:
Vector result = new Vector();
ResultSetMetaData rsm = rs.getMetaData();
while(rs.next){
result.add(rs.getString(1));
...
for(j=x;j<rsm.getColumnCount();j++){>
if (rsm.getColumnType.equals("CHAR")){
.....
result.add(rs.getString(j));
}
else{
.....
result.add(rs.getBigDecimal(j));
}
}
}
The performance of this loop is very low and in many case the application crash.
My question is, how can I handle these kinds of huge resultsets and how can improve the application performance.
Note:
I have noticed that my DBMS (Oracle 8) execute the query in few millisecond.
Regards,
Antonio S.
 
Timothy Willard
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am also about to embark on a project that may also deal with large recordsets. I believe my application will also crash out. Any suggestions from someone with experience here would be greatly appreciated.
 
Ernest Lee
Ranch Hand
Posts: 145
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Please tell us more about the purpose of your application.
 
antonio saginario
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Ernest Lee:
Please tell us more about the purpose of your application.

Please forgive me for the missing informations. The application consists of several servlets which on user request produce dynamic SQL queries. These queries can
return from 0 to more than 100000 records. The results are shown to the user by a pagination in the memory.
The ResultSet reading, which follows more or less the scheme that I described in the previous mail, produces the application crashing when the two nested cycles are very long. I made a kind of optimization, managing on my best the local objects creation and the Vector presizing, but actually I can't understand if the problem is the memory size or if it depends on other factors as for example the RecordSet reading.
Any suggestion will be pleasant. Thank you.

Best Wishes.

 
Ernest Lee
Ranch Hand
Posts: 145
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
my suggestion:
1) use something like this "select * from tables limit 1, 1000" is ur RDBMS support.
2) save all record set in a view (or temp table) with primary key "KEY1".. and retreive records from there using "select * from temp_table where KEY1 > xxx and KEY1 < xxxx", etc...
3) is ur interested recordset are in sorted order..... using different query for different client's page view.
4) expand ur hardware.
hope it helps
 
karl koch
Ranch Hand
Posts: 388
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
if you know the format and size of result, then you can use arrays instead of Vector.
also or use ArrayList instead of Vector(). ArrayList is not synchronized and therefore faster than Vector (what if been told)
k
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic