Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Does Next call on Resultset, result in a round trip call to DB, when fetchsize is set ?

 
Kumar Raja
Ranch Hand
Posts: 547
2
Hibernate Java Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello All,

I'm a bit confused with the concept of Fetch size being set on ResultSet and Statement. Based on the information provided here, I understand that Fetchsize set on Statement or ResultSet will determine the number of round trip calls.

My question is, hypothetically assuming that my table has 1000 rows, and I set my Fetchsize to 50

Now, when I execute my query say SELECT * FROM TABLE (with FETCHSIZE preset to 50), and I call Next() on resultset,

1) Does each call to Next() on ResultSet, results in a round trip call to DB, where the fetched 50 rows are stored in some cache on DB. This does not necessarily mean that SELECT query is reexecuted, but requires a TCP/IP call to DB.
2) Does each call to Next() on ResultSet, results in a round trip call to DB, which involves a TCP/IP call and also a reexecution of the query.
3) or the first invocation returns all the 50 rows, but stored some on some internal cache of the ResultSet, and Next() will simply iterate through that cache. No TCP or DB query execution done.

Also, after 50 rows are returned and processed, and if there is next() call again, would the query be executed again and returns 50 rows and this continues until all the 1000 are returned.

My understanding is, option 1 is correct. i.e, next() call will still require a TCP call to DB server, but instead of executing the query, it fetches the next row from some cache maintained at DB and that is returned. Please correct me, if I'm wrong.


Thanks

 
Ulf Dittmer
Rancher
Posts: 42969
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Are you specifically asking about the Oracle DB? Because that's what that documentation talks about, not JDBC in general.

I sure hope that #3 would be the case, but I think the details are up to the JDBC driver (and thus DB-specific).
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
As Ulf says, this is obviously implementation dependent.

In the case of Oracle, #3 is correct. Furthermore, after calling next() 50 times, the query is not re-executed. A cursor is kept open in the database and the database picks up where it ended last time and returns next 50 rows in one network call.
 
Kumar Raja
Ranch Hand
Posts: 547
2
Hibernate Java Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic