This week's book giveaway is in the OO, Patterns, UML and Refactoring forum. We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma on-line! See this thread for details.
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
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
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 ]
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 ]