• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Ranch Hand
Posts: 558
2
Hibernate Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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

 
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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).
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 558
2
Hibernate Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thank you
reply
    Bookmark Topic Watch Topic
  • New Topic