permaculture playing cards*
The moose likes Performance and the fly likes ResultSet.last() operation Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Performance
Bookmark "ResultSet.last() operation" Watch "ResultSet.last() operation" New topic
Author

ResultSet.last() operation

Himanshu Jhamb
Ranch Hand

Joined: Aug 01, 2001
Posts: 134
Hi
I need to know the number of rows retrieved from a SELECT query before I start parsing through them.
The way I do it right now is...
rs.last();
int numOfRowsRetrieved = rs.getRow();
rs.beforeFirst();
where rs is the ResultSet Object returned by the query.
My concern is that I might be causing too much of overhead by placing the cursor on the last row & then getting it back to the first row. I would like to know if there is a simpler way to get the number of rows retrieved without moving the cursor around like this.
I know the ResultSetMetaData gives all kind of info for the Columns ... is there something like that for rowCount ?
thanks in advance


Himanshu Jhamb<br />SCJP2 v1.2 (April 2002)<br />SCJP2 v1.4 (May 2002)
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

from the JDBC implementations that I've worked with, the rs.last() is nothing but a loop similar to
while( rs.next() ); //loop to end
rs.previous(); //go back to last record
Most people think that somehow it magically jumps to the last row in the resultset, but it doesn't. Also, since the resultset is scrollable, every record will be stored in memory as it loops through the resultset to find the end. This is not good for resultsets with a large number of rows. Non-scrollable results only store the number of rows in memory specified by the fetch size.
Recommendation:
execute a "select count(*) from...." to find out the number of results that will be returned, then execute the SQL statement that returns the actual results.
I'm not a big fan of scrollable resultsets either. They tend to decrease performance, increase overhead, tie up database resources, and are not always portable between databases.
just my $.002
Jamie
Billy Talton
Greenhorn

Joined: Sep 17, 2002
Posts: 19
Use the CachedRowSet API from Sun. It has a "count" method that is optimized, disconnected and efficient.
http://developer.java.sun.com/developer/Books/JDBCTutorial/chapter5.html
Hope this helps.
-Billy
Himanshu Jhamb
Ranch Hand

Joined: Aug 01, 2001
Posts: 134
Thanks for the responses.
The CachedRowSet is not a possibility since I believe, its only available if I am using > JDK 1.4
I am still on JDK 1.3.1 & it doesn't have any APIs for RowSet or CachedRowSet.
I am not too big a fan of hitting the DB with multiple queries... and doing a "SELECT count(*) ... " is too much of an overhead, in my humble opinion, for something as simple as getting the number of rows returned.
One thing though... my ResultSet is guaranteed to be not too big. So, would this last() operation be a tolerable overhead ??? Please comment
Also, any other suggestions are more than welcome.
thanks
[ November 27, 2002: Message edited by: Himanshu Jhamb ]
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

try it both ways. Use the one that works best for you. There are so many variables just in the JDBC implementation you are using, never mind database structure/indexes that it is hard for anyone to say which will be faster for you. In general, the extra time it takes to fire off another query may be less than the performance hit you take for creating/using a scrollable resultset. Then again maybe it might be the opposite, depending on those variables I mentioned earlier. If I were you, I'd create the most maintainable code you can, then start performance tuning if the app doesn't make performance expectations.
Jamie
Jamie
[ November 27, 2002: Message edited by: Jamie Robertson ]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: ResultSet.last() operation
 
Similar Threads
adding multiple rows to a list
ResultSet and where the data returned actually is located.
retrieve rows
Java Result Set
ResultSet