This week's book giveaway is in the Java 8 forum.
We're giving away four copies of Java 8 in Action and have Raoul-Gabriel Urma, Mario Fusco, and Alan Mycroft on-line!
See this thread for details.
The moose likes Java in General and the fly likes huge recordset problem Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Java » Java in General
Bookmark "huge recordset problem" Watch "huge recordset problem" New topic
Author

huge recordset problem

antonio saginario
Greenhorn

Joined: Nov 18, 2001
Posts: 5
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

Joined: Apr 03, 2001
Posts: 25
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

Joined: Aug 20, 2001
Posts: 145
Please tell us more about the purpose of your application.
antonio saginario
Greenhorn

Joined: Nov 18, 2001
Posts: 5
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

Joined: Aug 20, 2001
Posts: 145
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

Joined: May 25, 2001
Posts: 388
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
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: huge recordset problem
 
Similar Threads
huge recordset problem
Using Vectors
how to improve my program code ?
Eclipse - how can i go from one Break Point to another Break Point
Handling huge resultset