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..
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
Your assumptions are correct.
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 shoot instead of using multiple roundtrips.
Only for Oracle you might want to set it since the default fetch size is just 10. For more details, check out this article.
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 setiing these two as well:
hibernate.order_inserts to truehibernate.order_updates to trueFor more details check out these two articles:
Fetch size does Statement.setFetchSize() while batch size is for Hibernate batching. Both configuration parameters are explained here. For hibernate batch refer here
Your understanding seems quite correct. I would refer you to the JBOSS documentation on Hibernate, the following chapter is on Batch processing. And this one on tweaking performance.
It's a good, easy to read source. It gives some suggestions on optimal values, but as CodeChimp mentioned, tuning is best done case by case and is a repeatable process over time.
