CODEWITHSUNDEEP
javajdbcresultset
vatsal mevada
vatsal mevada

Reputation: 5616

What are the advantages of result set TYPE_FORWARD_ONLY over TYPE_SCROLL_INSENSITIVE?

So a TYPE_SCROLL_INSENSITIVE result set allows me to scroll in both directions where as TYPE_FORWARD_ONLY only allows me to move forward.

Since TYPE_SCROLL_INSENSITIVE allows me to do what TYPE_FORWARD_ONLY allows, why do I need TYPE_FORWARD_ONLY at all? I assume that TYPE_FORWARD_ONLY might be better performance wise but I would like to know how?

Upvotes: 2

Views: 1911

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 109016

In my experience, most cases you will only need to read data in one direction. To be honest, I have never even had to use anything other than TYPE_FORWARD_ONLY (except when testing JDBC driver implementations). I know it is sometimes used in interactive GUI applications, that provide editable table views, although I would still consider different designs even for those type of systems.

Reading a result set in one direction has a number of advantages:

  • It is very simple, so most database systems support it, and it is usually pretty fast
  • It has less overhead, because the server just needs to supply a row once and can then forget about it

The disadvantages:

  • You can't scroll back or jump to 'random' points in the result set (questionable IMHO, I've never needed it)
  • On some database systems or drivers (as indicated by the - now deleted - answer of sForSujit), a forward only result set will need to be read completely (or the driver will have the consume the rest on close)

In contrast, scrolling result sets have as advantage basically the disadvantages of forward only.

Disadvantages for scrollable result sets however are:

  • More overhead as the server needs to 'remember' the rows it supplied previously, or to be able to scroll/jump ahead (although there are tricks that reduce that overhead in exchange for more complexity)
  • Not all database systems support it, which means it might then be 'simulated' by the driver. This can lead to additional memory and network overhead (because the entire result set is fetched into memory).
  • Depending on the scroll type (especially type sensitive), rows might be fetched one-by-one, which increases the number of round-trips necessary to fetch all data (and if it does fetch multiple, jumping 'randomly' through the result set might mean the additional data retrieved is discarded unused).

Note, this list is probably not complete.

Upvotes: 4

Related Questions