This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes Java in General and the fly likes Handling huge resultset Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Java » Java in General
Bookmark "Handling huge resultset" Watch "Handling huge resultset" New topic
Author

Handling huge resultset

thilak subbiah
Greenhorn

Joined: Nov 16, 2000
Posts: 9
Hi,
When i execute the following statement,
ResultSet rs = stmt.executeQuery("Select * from emp");
returns a ResultSet which contains say 10000 records.Keeping this resultset as opened will affect the performance.so, we have to close that rs at once.My question is, how can we handle these kinds of huge resultsets.
Thanks in advance
Regards,
Thilak.V
------------------
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1121

Lots of missing information here that affects the answer. A ResultSet is like a database cursor. One of the details you don't mention is the DBMS you are using. With my experience (with both Oracle and Informix), a database cursor is something available via the DBMS's procedural language extension - in Oracle this is PL/SQL and in Informix it is SPL. Both these DBMS's (Oracle and Informix) know how to handle huge result sets via cursors, so a java.sql.ResultSet should also be handled just as well. Therefore, if the cursors don't cause performance problems, why should the ResultSet? There are obviously some other details you haven't mentioned.
We use RMI to return ResultSets to a remote client by building java.util.Collections. The ResultSet remains open on the server and the client can navigate the ResultSet remotely and receives Collections that can be of varying sizes from one row (of the ResultSet) to the entire ResultSet (memory permitting). We use large ResultSets and don't notice any performance problems. If, however, you open several ResultSets simultaneously, then you start to see problems.
More details about your situation would help. What is your architecture - client/server, remote clients, local clients? What is your DBMS - Oracle, Informix, SQL Server, Cloudscape? Do you have a java application or applet (or servlet or JSP or what)?
Cheers,
Avi.
thilak subbiah
Greenhorn

Joined: Nov 16, 2000
Posts: 9
Hi Avi,
Thanks for your information. sorry for not mentioning full details.
I am accessing the Oracle8 database from servlet.I am getting maximum cursors exceeded error because of many opened Resultsets.So, i want to store those huge resultset in some other temporary object and close the resultset at once.
Regards,
Thilak.V

Originally posted by Avi Abrami:
Lots of missing information here that affects the answer. A ResultSet is like a database cursor. One of the details you don't mention is the DBMS you are using. With my experience (with both Oracle and Informix), a database cursor is something available via the DBMS's procedural language extension - in Oracle this is PL/SQL and in Informix it is SPL. Both these DBMS's (Oracle and Informix) know how to handle huge result sets via cursors, so a java.sql.ResultSet should also be handled just as well. Therefore, if the cursors don't cause performance problems, why should the ResultSet? There are obviously some other details you haven't mentioned.
We use RMI to return ResultSets to a remote client by building java.util.Collections. The ResultSet remains open on the server and the client can navigate the ResultSet remotely and receives Collections that can be of varying sizes from one row (of the ResultSet) to the entire ResultSet (memory permitting). We use large ResultSets and don't notice any performance problems. If, however, you open several ResultSets simultaneously, then you start to see problems.
More details about your situation would help. What is your architecture - client/server, remote clients, local clients? What is your DBMS - Oracle, Informix, SQL Server, Cloudscape? Do you have a java application or applet (or servlet or JSP or what)?
Cheers,
Avi.


------------------
Peter den Haan
author
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
Originally posted by thilak subbiah:

I am accessing the Oracle8 database from servlet.I am getting maximum cursors exceeded error because of many opened Resultsets. So, i want to store those huge resultset in some other temporary object and close the resultset at once.

Two things.
First, the maximum number of cursors Oracle gives you by default can usually be increased significantly without really impacting performance -- certainly less than the impact keeping huge result sets in-memory might have -- so your solution may be as simple as that.
Second, might it be possible to rewrite the algorithms so that fewer cursors need to be open simultaneously?
Regards,
Peter
[This message has been edited by Peter den Haan (edited December 06, 2000).]
Carl Trusiak
Sheriff

Joined: Jun 13, 2000
Posts: 3340
I've had similar problems with my servlet and an Oracle database. Most of my ResultSet are relatively low a couple of hundred rows. Oracle desn't seem to relably release a cursor that jdbc opens even if you close and set to null the associated ResultSet. I corrected this problem inside my Connection pool by refreshing (closing and reopening) idle connections that have been used more than about 50 times or has been open for more than 30 minutes.
Hope this helps


I Hope This Helps
Carl Trusiak, SCJP2, SCWCD
Matt O'Keefe
Greenhorn

Joined: Nov 28, 2000
Posts: 7
I've seen a "cursor leak" problem too, and it was the result of not closing either ResultSets or Statements - I can't remember which. Try using the close() method on Statement objects, which will also call close() on any associated ResultSet objects.
BTW, here's a SQL statement that a quick search at Ari Kaplan's Oracle tips site turned up (http://www.arikaplan.com/oracle.html) - this should reveal the number of open cursors at a given time and help you track down any potential "cursor leaks":
select * from v$sysstat where name = 'opened cursors current';
alex almero
Greenhorn

Joined: Jul 19, 2001
Posts: 6
Originally posted by Avi Abrami:
Lots of missing information here that affects the answer. A ResultSet is like a database cursor. One of the details you don't mention is the DBMS you are using. With my experience (with both Oracle and Informix), a database cursor is something available via the DBMS's procedural language extension - in Oracle this is PL/SQL and in Informix it is SPL. Both these DBMS's (Oracle and Informix) know how to handle huge result sets via cursors, so a java.sql.ResultSet should also be handled just as well. Therefore, if the cursors don't cause performance problems, why should the ResultSet? There are obviously some other details you haven't mentioned.
We use RMI to return ResultSets to a remote client by building java.util.Collections. The ResultSet remains open on the server and the client can navigate the ResultSet remotely and receives Collections that can be of varying sizes from one row (of the ResultSet) to the entire ResultSet (memory permitting). We use large ResultSets and don't notice any performance problems. If, however, you open several ResultSets simultaneously, then you start to see problems.
More details about your situation would help. What is your architecture - client/server, remote clients, local clients? What is your DBMS - Oracle, Informix, SQL Server, Cloudscape? Do you have a java application or applet (or servlet or JSP or what)?
Cheers,
Avi.

i had the same problem, with an application hosted on iPlanet 4.1 server using informix7.3 DB on HPUX11. we're using java servlets for the application. the query was so slow from the client. do you think that cursor can help us solve this problem or are there any solutions available?
thanks
alex
 
 
subject: Handling huge resultset
 
Similar Threads
ResultSet.close()
handle empty recordset
Problem in getting multiple resultsets from Oracle Stored Procedure
Where is Implementation for this method
returning a result set