• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

huge recordset problem

 
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 145
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 388
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
reply
    Bookmark Topic Watch Topic
  • New Topic