Is while(rs.next()) faster than a series of rs.absolute()

Question

Suppose to have a ResultSet rs with n object.

This code:

while(rs.next()) {
   // do something on rs
}

is algoritmically equal to this code (i.e. both gave the same result):

for(i=1; i<=n; i++) {
   rs.absolute(i)
   // do something on rs
}

But are this equivalant on terms of throughouts? Is the first faster? Or, for a given i, rs.next() is just a wrapper for rs.absolute(i+1)?

Best regards MC

What stops you from testing it yourself? Most likely, the difference is negigible so the code that is easier to read should be used. — Klas Lindbäck, Dec 19 '12 at 14:32
@KlasLindbäck Writing a correct microbenchmark is surprisingly [nontrivial](http://stackoverflow.com/questions/504103/how-do-i-write-a-correct-micro-benchmark-in-java). — Michael McGowan, Dec 19 '12 at 15:03
With `rs.next()` you don't need to know the result set size beforehand, with `rs.absolute()` you need to. Which means a second query beforehand. — BalusC, Dec 19 '12 at 15:04
@KlasLindbäck It seemed an intersting question, with a good margin of debate. — BAD_SEED, Dec 19 '12 at 15:10

Marko Topolnik · Accepted Answer · 2012-12-19T14:59:59.260

6

rs.next demands a simpler kind of database cursor (FORWARD_ONLY) than rs.absolute so in most cases you will degrade performance/resource efficiency with rs.absolute. In certain cases, where there is no optimization for a FORWARD_ONLY cursor anyway, you may get the same performance.

Some drivers may allow absolute calls even with FORWARD_ONLY, validating that the requested record is the next one, but again others may throw an exception regardless.

edited Dec 19 '12 at 14:59

answered Dec 19 '12 at 14:31

Marko Topolnik

195,646
29
319
436

So if I declared my PreparedStatement as prepareStatement( SQL, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE ); the two blocks of code are absolutely equals. Right? – BAD_SEED Dec 19 '12 at 14:36
This cannot be answered in the general because these are all just hints to the driver. There is less chance of a difference, true, but I'm sure there are counterexamples as well. – Marko Topolnik Dec 19 '12 at 14:43
@Mariano there is no way to confirm that, as it will strongly depend on the implementation of the driver and the behavior of the underlying database. In general I'd say: use `rs.next()` if you want to go through a ResultSet sequentially, but that is just a gut feeling – Mark Rotteveel Dec 19 '12 at 15:54
1

@MarkRotteveel I think we can safely say that you can't go wrong with `rs.next()`: there is practically no chance to have better performance with anything else. – Marko Topolnik Dec 19 '12 at 18:46
@MarkoTopolnik I agree with you, but as I haven't actually seen the numbers (and I know how vastly different implementations of ResultSet can be), I didn't want to make an absolute statement about all JDBC implementations – Mark Rotteveel Dec 20 '12 at 08:44
@MarkRotteveel True, someone, somewhere can always decide to write an absurdly broken JDBC driver. It wouldn't be the first time, just look at some absurdities in Oracle's driver :) – Marko Topolnik Dec 20 '12 at 08:48

score 2 · Answer 2 · answered Dec 19 '12 at 14:33

The former rs.next() might be faster, although it depends on the underlying implementation and in reality you might get the same performance due to the internal optimizations.

The driver might not be taking the current position into account when doing rs.absolute() so it might have slightly a little bit overhead, while in rs.next() its only moving forward.

So if you need to iterate through all the results, just use rs.next(), if you need to skip to a specific results use rs.absolute().

Remember that the result set is in fact often being handled remotely by the database (unless its a CachedResultSet), so going to and fro might involve more overheads than you think.

Is while(rs.next()) faster than a series of rs.absolute()

2 Answers2