Scrollable ResultSet performance

Blog
The best minds from Teradata, our partners, and customers blog about whatever takes their fancy.
Teradata Employee

The Teradata JDBC Driver provides both forward-only and scrollable result sets. With a forward-only result set, an application can only fetch rows in the forward direction, using the ResultSet.next method. A scrollable result set enables an application to fetch rows in any order.

Since a scrollable result set offers significantly more functionality than a forward-only result set, application developers may wonder whether there is a performance penalty for using a scrollable result set.

Question: What kind of performance can I expect when fetching rows forward (first-to-last) from a scrollable result set?

Answer: This will be comparable in efficiency to using a forward-only result set. In other words, there is no significant penalty when fetching forward through a scrollable result set.

Question: What about fetching rows backwards (last-to-first) from a scrollable result set?

Answer: This will be OK for a small result set whose total data (rows and columns) fits within 1 MB. For a result set larger than 1 MB, fetching backwards will be very slow, i.e. 10 to 100 times slower than fetching forward.

Question: What about directly accessing rows of the scrollable result set in some arbitrary order?

Answer: This will be OK for a small result set whose total data (rows and columns) fits within 1 MB. For a result set larger than 1 MB, random fetching is likely to be slower than fetching forward.

To summarize, the Teradata JDBC Driver caches only the most recent 1 MB response message from the Teradata Database.

With random fetching, if the desired row is in the cache, then the fetch will be fast. If the row isn't in the cache, then a round-trip to the Teradata Database must occur.

With backwards fetching for a result set larger than 1 MB, the desired row is never in the cache, so every row fetch causes a round-trip to the Teradata Database.