This week's book giveaway is in the Artificial Intelligence and Machine Learning forum.
We're giving away four copies of TensorFlow 2.0 in Action and have Thushan Ganegedara on-line!
See this thread for details.
Win a copy of TensorFlow 2.0 in Action this week in the Artificial Intelligence and Machine Learning forum!
  • 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Paul Clapham
  • Bear Bibeault
  • Jeanne Boyarsky
Sheriffs:
  • Ron McLeod
  • Tim Cooke
  • Devaka Cooray
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Jj Roberts
  • Stephan van Hulst
  • Carey Brown
Bartenders:
  • salvin francis
  • Scott Selikoff
  • fred rosenberger

Handling huge resultset

 
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
------------------
 
Ranch Hand
Posts: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.



------------------
 
author
Posts: 3252
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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).]
 
Sheriff
Posts: 3341
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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';
 
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
 
I’m tired of walking, and will rest for a minute and grow some wheels. This is the promise of this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic