# Default\_batch\_fetch\_size is unnecessarily respected when loading a one-to-many collection from a single entity object

**URL:** https://discourse.hibernate.org/t/default-batch-fetch-size-is-unnecessarily-respected-when-loading-a-one-to-many-collection-from-a-single-entity-object/7302
**Category:** Hibernate ORM
**Created:** [February 26, 2023, 10:17pm UTC](https://discourse.hibernate.org/t/default-batch-fetch-size-is-unnecessarily-respected-when-loading-a-one-to-many-collection-from-a-single-entity-object/7302 "2023-02-26T22:17:35Z")
**Posts on this page:** 3
**Page:** 1

<div class="post-metadata">

### Author: ![mattwang75](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/mattwang75/32/2279_2.png) [@mattwang75](https://discourse.hibernate.org/u/mattwang75)
#### Post date: [February 26, 2023, 10:17pm UTC](https://discourse.hibernate.org/t/default-batch-fetch-size-is-unnecessarily-respected-when-loading-a-one-to-many-collection-from-a-single-entity-object/7302/1 "2023-02-26T22:17:35Z")

</div>

I created a simple project to demonstrate this issue: [GitHub - mattwang75/Hibernate6-DuplicateParametersByBatchFetchSize](https://github.com/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.

```auto
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]

```

---

<div class="post-metadata">

### Author: ![mbladel](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/mbladel/32/2128_2.png) [@mbladel](https://discourse.hibernate.org/u/mbladel)
#### Post date: [February 27, 2023, 9:30am UTC](https://discourse.hibernate.org/t/default-batch-fetch-size-is-unnecessarily-respected-when-loading-a-one-to-many-collection-from-a-single-entity-object/7302/2 "2023-02-27T09:30:03Z")

</div>

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

This looks like a similar issue to [[HHH-16043] - Hibernate JIRA](https://hibernate.atlassian.net/browse/HHH-16043), 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?

---

<div class="post-metadata">

### Author: ![mattwang75](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/mattwang75/32/2279_2.png) [@mattwang75](https://discourse.hibernate.org/u/mattwang75)
#### Post date: [February 27, 2023, 5:01pm UTC](https://discourse.hibernate.org/t/default-batch-fetch-size-is-unnecessarily-respected-when-loading-a-one-to-many-collection-from-a-single-entity-object/7302/3 "2023-02-27T17:01:23Z")

</div>

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