• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

ResultSet.last() operation

 
Himanshu Jhamb
Ranch Hand
Posts: 134
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 19
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 134
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic