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

fetchSize property in the @NamedNativeQuery

 
S Majumder
Ranch Hand
Posts: 349
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All ,
I am using different fetchSize to get data by using @NamedNativeQuery , but every time am getting the same number of records .



In the DB there are 2 records available for Stock. When I change the fetchSize = 1 , am getting 2 records instead of 1 .




Every time the list's size am getting 2 .

Any idea why ?

Let me know if you need further query on this ....

Thanks in advance ,
Satya
 
Dave Tolls
Ranch Hand
Posts: 2110
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
fetchSize does not affect the number of rows returned by your query, it affects the number of rows read by the JDBC driver on a single round trip to the server, of which there may be many for a single query.
 
S Majumder
Ranch Hand
Posts: 349
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave Tolls wrote:fetchSize does not affect the number of rows returned by your query, it affects the number of rows read by the JDBC driver on a single round trip to the server, of which there may be many for a single query.


So if I change the fetchSize from 2 to 1 ,,where it will effect ?

Satya
 
Dave Tolls
Ranch Hand
Posts: 2110
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
S Majumder wrote:
Dave Tolls wrote:fetchSize does not affect the number of rows returned by your query, it affects the number of rows read by the JDBC driver on a single round trip to the server, of which there may be many for a single query.


So if I change the fetchSize from 2 to 1 ,,where it will effect ?

Satya


It affects the Driver interaction with the database when returning results.
With a fetchSize of 2 then the database would return 2 results, when those 2 have been read from the ResultSet then more will be requested. If there are none left then the ResultSet returns false for hasNext().
With a fetchSize of one then the driver will make a request for more rows everytime a hasNext() is made...retrieving one row at a time.

Neither of those is terribly efficient if you are expecting lots of rows as it involves lots of calls to the db by the driver in order to retrieve everything.

It's really something you only set when you have determined that there is a performance issue with the default fetch size.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic