What is the difference between hibernate.jdbc.fetch_size and hibernate.jdbc.batch_size?

Question

I am trying to tune my application, came across some blogs speaking about the batch fetch and batch select and putting my understanding as follows.

• hibernate.jdbc.fetch_size - Used to specify number of rows to be fetched in a select query.
• hibernate.jdbc.batch_size - Used to specify number of inserts or updates to be carried out in a single database hit.

Please let me know whether my understanding is correct or not? Also what are the optimal values for the above parameters..

Answer 1

Both of these options set properties within the JDBC driver. In the first case, hibernate.jdbc.fetch_size sets the statement's fetch size within the JDBC driver, that is the number of rows fetched when there is more than a one row result on select statements.

In the second case, hibernate.jdbc.batch_size determines the number of updates (inserts, updates and deletes) that are sent to the database at one time for execution. This parameter is necessary to do batch inserts, but must be coupled with the ordered inserts parameter and the JDBC driver's capability to rewrite the inserts into a batch insert statement.

See this link

Answer 2

Your assumptions are correct.

hibernate.jdbc.fetch_size

The hibernate.jdbc.fetch_size Hibernate configuration property is used for setting the JDBC Statement#setFetchSize property for every statement that Hibernate uses during the currently running Persistence Context.

Usually, you don't need to set this property as the default is fine, especially for MySQL and PostgreSQL which fetch the entire ResultSet in a single database roundtrip. Because Hibernate traverses the entire ResultSet, you are better off fetching all rows in a single shot instead of using multiple roundtrips.

Only for Oracle, you might want to set it since the default fetchSize is just 10.

hibernate.jdbc.batch_size

The hibernate.jdbc.batch_size property is used to batch multiple INSERT, UPDATE, and DELETE statements together so that they can be set in a single database call.

If you set this property, you are better off setting these two as well:

• hibernate.order_inserts to true
• hibernate.order_updates to true