wood burning stoves 2.0*
The moose likes JDBC and the fly likes About ResultSet and used memory Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "About ResultSet and used memory" Watch "About ResultSet and used memory" New topic
Author

About ResultSet and used memory

Andrew Nomos
Greenhorn

Joined: May 31, 2005
Posts: 18
Hello,

in the JDBC API it is said that a ResultSet is an object that maintains a cursor pointing to its current row of data.
What does that mean in terms of memory?

For example, when I execute a database query that returns 500.000 records, the ResultSet will not acctually keep the 500.000 records, won't it? But what actually gets send to my machine?

Any comments are highly appreciated.

Thanks,
Andrew
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Originally posted by Andrew Nomos:
Hello,

in the JDBC API it is said that a ResultSet is an object that maintains a cursor pointing to its current row of data.


Yes this is correct


What does that mean in terms of memory?


It means that there is nothing being hold in your JVM's memory



For example, when I execute a database query that returns 500.000 records, the ResultSet will not acctually keep the 500.000 records, won't it? But what actually gets send to my machine?


Whenever you execute any query, a cursor is maintianed at database and every resultset.next() makes cursor to move at next row.
Hence every rs.next() casues a round trip to database, and current row is sent to your machine.

hope this answers to your question
Shailesh
[ June 30, 2005: Message edited by: Shailesh Chandra ]

Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
Alin Sinpalean
Greenhorn

Joined: Jun 02, 2005
Posts: 6
Originally posted by Shailesh Chandra:
It means that there is nothing being hold in your JVM's memory


Not necessarily. There are cases (such as when using scrollable result sets with Oracle or when executing simultaneous statements) when the JDBC driver will have no choice but to create a client side cursor, i.e. cache the whole result set to memory and/or disk. Read the documentation of the driver(s) you are using to see when and whether this happens.


Whenever you execute any query, a cursor is maintianed at database and every resultset.next() makes cursor to move at next row.
Hence every rs.next() casues a round trip to database, and current row is sent to your machine.


Again, this is not entirely correct. Except the cases described above, when the whole result set is retrieved and cached by the driver, there's also such a thing as fetch size (see Statement and ResultSet.setFetchSize()). A JDBC driver will typically fetch more than one row at once (e.g. 100 rows) and provide you with rows from that buffer; when you've gotten to the end of that buffer the driver will fetch another set of rows. The number of rows fetched can be controlled via the setFetchSize() methods mentioned above.

Alin,
The jTDS Project.
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Alin,

that was my level one info to poster, I know the facts told by you..seems its not my day today

thanks for a detailed answer and Welcome to Ranch...

Shailesh
[ June 30, 2005: Message edited by: Shailesh Chandra ]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: About ResultSet and used memory