Default_batch_fetch_size is unnecessarily respected when loading a one-to-many collection from a single entity object

Hibernate ORM
1

I created a simple project to demonstrate this issue: GitHub - mattwang75/Hibernate6-DuplicateParametersByBatchFetchSize

In Hibernate 5, we configured hibernate.default_batch_fetch_size=50 for performance optimization when multiple entity objects are to be hydrated. This will reduce database round-trips.

However, Hibernate 6 is doing something questionable. It seems to wrongly respect this setting when loading a @OneToMany collection from a single entity object. I captured the SQL it produced (see below). This query is supposed to load a collection of Observations associated with a single Interaction. But notice the Interaction id 825298930 is repeated 50 times!!! Not only it incurs wasted effort to bind the same parameter value 50 times unnecessarily, it also causes our Postgres DB much longer to execute the query (measured 50 msec vs. 2.5 sec, which is 50x slower).

default_batch_fetch_size has its useful places to optimize query performance by reducing database round-trips (such as when doing FetchType.SELECT), but not in this occasion. If I don’t set default_batch_fetch_size, then the generated SQL has a WHERE clause of o1_0.ID_INTERACTION=? which is the desired query. But I do want to set default_batch_fetch_size because I want that optimization in many other places.

This seems a regression because it behaves properly in Hibernate 5.

2023-02-25 21:22:11,057 DEBUG [5@cvm-dev03/2] org.hibernate.SQL: [OH_ACRM]
    select
        o1_0.ID_INTERACTION,
        o1_0.ID,
        o1_0.ID_STANDARDIZED_CODE,
        o1_0.INTERACTION_DATE,
        o1_0.MESSAGE_ID,
        o1_0.NATURAL_KEY,
        o1_0.OBSERVATION_DATE,
        o1_0.UPDATED,
        o1_0.VALUE_BOOL,
        o1_0.VALUE_DATE,
        o1_0.VALUE_ENUM,
        o1_0.VALUE_NUMBER,
        o1_0.VALUE_TEXT,
        o1_0.VALUE_VAULT,
        o1_0.VALUE_VAULT_HASH
    from
        OBSERVATION o1_0
    where
         o1_0.ID_INTERACTION in(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

2023-02-25 21:22:11,057 TRACE [5@cvm-dev03/2] org.hibernate.orm.jdbc.bind: [OH_ACRM] binding parameter [1] as [BIGINT] - [825298930]
2023-02-25 21:22:11,057 TRACE [5@cvm-dev03/2] org.hibernate.orm.jdbc.bind: [OH_ACRM] binding parameter [2] as [BIGINT] - [825298930]
2023-02-25 21:22:11,057 TRACE [5@cvm-dev03/2] org.hibernate.orm.jdbc.bind: [OH_ACRM] binding parameter [3] as [BIGINT] - [825298930]
2023-02-25 21:22:11,057 TRACE [5@cvm-dev03/2] org.hibernate.orm.jdbc.bind: [OH_ACRM] binding parameter [4] as [BIGINT] - [825298930]
2023-02-25 21:22:11,057 TRACE [5@cvm-dev03/2] org.hibernate.orm.jdbc.bind: [OH_ACRM] binding parameter [5] as [BIGINT] - [825298930]
2023-02-25 21:22:11,057 TRACE [5@cvm-dev03/2] org.hibernate.orm.jdbc.bind: [OH_ACRM] binding parameter [6] as [BIGINT] - [825298930]
2023-02-25 21:22:11,057 TRACE [5@cvm-dev03/2] org.hibernate.orm.jdbc.bind: [OH_ACRM] binding parameter [7] as [BIGINT] - [825298930]
2023-02-25 21:22:11,057 TRACE [5@cvm-dev03/2] org.hibernate.orm.jdbc.bind: [OH_ACRM] binding parameter [8] as [BIGINT] - [825298930]
2023-02-25 21:22:11,057 TRACE [5@cvm-dev03/2] org.hibernate.orm.jdbc.bind: [OH_ACRM] binding parameter [9] as [BIGINT] - [825298930]
2023-02-25 21:22:11,057 TRACE [5@cvm-dev03/2] org.hibernate.orm.jdbc.bind: [OH_ACRM] binding parameter [10] as [BIGINT] - [825298930]
2023-02-25 21:22:11,058 TRACE [5@cvm-dev03/2] org.hibernate.orm.jdbc.bind: [OH_ACRM] binding parameter [11] as [BIGINT] - [825298930]
2023-02-25 21:22:11,058 TRACE [5@cvm-dev03/2] org.hibernate.orm.jdbc.bind: [OH_ACRM] binding parameter [12] as [BIGINT] - [825298930]
2023-02-25 21:22:11,058 TRACE [5@cvm-dev03/2] org.hibernate.orm.jdbc.bind: [OH_ACRM] binding parameter [13] as [BIGINT] - [825298930]
2023-02-25 21:22:11,058 TRACE [5@cvm-dev03/2] org.hibernate.orm.jdbc.bind: [OH_ACRM] binding parameter [14] as [BIGINT] - [825298930]
2023-02-25 21:22:11,058 TRACE [5@cvm-dev03/2] org.hibernate.orm.jdbc.bind: [OH_ACRM] binding parameter [15] as [BIGINT] - [825298930]
2023-02-25 21:22:11,058 TRACE [5@cvm-dev03/2] org.hibernate.orm.jdbc.bind: [OH_ACRM] binding parameter [16] as [BIGINT] - [825298930]
2023-02-25 21:22:11,058 TRACE [5@cvm-dev03/2] org.hibernate.orm.jdbc.bind: [OH_ACRM] binding parameter [17] as [BIGINT] - [825298930]
2023-02-25 21:22:11,058 TRACE [5@cvm-dev03/2] org.hibernate.orm.jdbc.bind: [OH_ACRM] binding parameter [18] as [BIGINT] - [825298930]
2023-02-25 21:22:11,058 TRACE [5@cvm-dev03/2] org.hibernate.orm.jdbc.bind: [OH_ACRM] binding parameter [19] as [BIGINT] - [825298930]
2023-02-25 21:22:11,058 TRACE [5@cvm-dev03/2] org.hibernate.orm.jdbc.bind: [OH_ACRM] binding parameter [20] as [BIGINT] - [825298930]
2023-02-25 21:22:11,058 TRACE [5@cvm-dev03/2] org.hibernate.orm.jdbc.bind: [OH_ACRM] binding parameter [21] as [BIGINT] - [825298930]
2023-02-25 21:22:11,058 TRACE [5@cvm-dev03/2] org.hibernate.orm.jdbc.bind: [OH_ACRM] binding parameter [22] as [BIGINT] - [825298930]
2023-02-25 21:22:11,058 TRACE [5@cvm-dev03/2] org.hibernate.orm.jdbc.bind: [OH_ACRM] binding parameter [23] as [BIGINT] - [825298930]
2023-02-25 21:22:11,058 TRACE [5@cvm-dev03/2] org.hibernate.orm.jdbc.bind: [OH_ACRM] binding parameter [24] as [BIGINT] - [825298930]
2023-02-25 21:22:11,058 TRACE [5@cvm-dev03/2] org.hibernate.orm.jdbc.bind: [OH_ACRM] binding parameter [25] as [BIGINT] - [825298930]
2023-02-25 21:22:11,058 TRACE [5@cvm-dev03/2] org.hibernate.orm.jdbc.bind: [OH_ACRM] binding parameter [26] as [BIGINT] - [825298930]
2023-02-25 21:22:11,058 TRACE [5@cvm-dev03/2] org.hibernate.orm.jdbc.bind: [OH_ACRM] binding parameter [27] as [BIGINT] - [825298930]
2023-02-25 21:22:11,058 TRACE [5@cvm-dev03/2] org.hibernate.orm.jdbc.bind: [OH_ACRM] binding parameter [28] as [BIGINT] - [825298930]
2023-02-25 21:22:11,058 TRACE [5@cvm-dev03/2] org.hibernate.orm.jdbc.bind: [OH_ACRM] binding parameter [29] as [BIGINT] - [825298930]
2023-02-25 21:22:11,058 TRACE [5@cvm-dev03/2] org.hibernate.orm.jdbc.bind: [OH_ACRM] binding parameter [30] as [BIGINT] - [825298930]
2023-02-25 21:22:11,058 TRACE [5@cvm-dev03/2] org.hibernate.orm.jdbc.bind: [OH_ACRM] binding parameter [31] as [BIGINT] - [825298930]
2023-02-25 21:22:11,058 TRACE [5@cvm-dev03/2] org.hibernate.orm.jdbc.bind: [OH_ACRM] binding parameter [32] as [BIGINT] - [825298930]
2023-02-25 21:22:11,058 TRACE [5@cvm-dev03/2] org.hibernate.orm.jdbc.bind: [OH_ACRM] binding parameter [33] as [BIGINT] - [825298930]
2023-02-25 21:22:11,058 TRACE [5@cvm-dev03/2] org.hibernate.orm.jdbc.bind: [OH_ACRM] binding parameter [34] as [BIGINT] - [825298930]
2023-02-25 21:22:11,058 TRACE [5@cvm-dev03/2] org.hibernate.orm.jdbc.bind: [OH_ACRM] binding parameter [35] as [BIGINT] - [825298930]
2023-02-25 21:22:11,058 TRACE [5@cvm-dev03/2] org.hibernate.orm.jdbc.bind: [OH_ACRM] binding parameter [36] as [BIGINT] - [825298930]
2023-02-25 21:22:11,059 TRACE [5@cvm-dev03/2] org.hibernate.orm.jdbc.bind: [OH_ACRM] binding parameter [37] as [BIGINT] - [825298930]
2023-02-25 21:22:11,059 TRACE [5@cvm-dev03/2] org.hibernate.orm.jdbc.bind: [OH_ACRM] binding parameter [38] as [BIGINT] - [825298930]
2023-02-25 21:22:11,059 TRACE [5@cvm-dev03/2] org.hibernate.orm.jdbc.bind: [OH_ACRM] binding parameter [39] as [BIGINT] - [825298930]
2023-02-25 21:22:11,059 TRACE [5@cvm-dev03/2] org.hibernate.orm.jdbc.bind: [OH_ACRM] binding parameter [40] as [BIGINT] - [825298930]
2023-02-25 21:22:11,059 TRACE [5@cvm-dev03/2] org.hibernate.orm.jdbc.bind: [OH_ACRM] binding parameter [41] as [BIGINT] - [825298930]
2023-02-25 21:22:11,059 TRACE [5@cvm-dev03/2] org.hibernate.orm.jdbc.bind: [OH_ACRM] binding parameter [42] as [BIGINT] - [825298930]
2023-02-25 21:22:11,059 TRACE [5@cvm-dev03/2] org.hibernate.orm.jdbc.bind: [OH_ACRM] binding parameter [43] as [BIGINT] - [825298930]
2023-02-25 21:22:11,059 TRACE [5@cvm-dev03/2] org.hibernate.orm.jdbc.bind: [OH_ACRM] binding parameter [44] as [BIGINT] - [825298930]
2023-02-25 21:22:11,059 TRACE [5@cvm-dev03/2] org.hibernate.orm.jdbc.bind: [OH_ACRM] binding parameter [45] as [BIGINT] - [825298930]
2023-02-25 21:22:11,059 TRACE [5@cvm-dev03/2] org.hibernate.orm.jdbc.bind: [OH_ACRM] binding parameter [46] as [BIGINT] - [825298930]
2023-02-25 21:22:11,059 TRACE [5@cvm-dev03/2] org.hibernate.orm.jdbc.bind: [OH_ACRM] binding parameter [47] as [BIGINT] - [825298930]
2023-02-25 21:22:11,059 TRACE [5@cvm-dev03/2] org.hibernate.orm.jdbc.bind: [OH_ACRM] binding parameter [48] as [BIGINT] - [825298930]
2023-02-25 21:22:11,060 TRACE [5@cvm-dev03/2] org.hibernate.orm.jdbc.bind: [OH_ACRM] binding parameter [49] as [BIGINT] - [825298930]
2

Hello @mattwang75, what version of Hibernate 6 are you using?

This looks like a similar issue to [HHH-16043] - Hibernate JIRA, which was resolved in Hibernate 6.1.7. Can you please test with the latest version and let me know if the problem still occurs?

1 Like
3

Yes, 6.1.7 fixed our problem. Thank you for the pointer.