Been reading this How to fix Hibernate's Warning "HHH000104: firstResult/maxResults specified with collection fetch" article or blog don’t know what it is about entity graph removing the HHH000104 warning, but what I don’t understand is if the entity graph query removes the problem internally and does the double select query in some magical way or do I still get the performance hit of the query being applied in memory?
If you try to fetch at least one collection + a bag i.e. List without @OrderColumn, then Hibernate ORM will fetch the bag with a separate select statement because the data usually can not be selected in a single query while retaining the correct result cardinality.
If you use a join fetch instead, you might cause result cardinality duplication i.e. your bag might contain the same element multiple times though it wouldn’t look like that if it were fetched separately.
Using a join fetch instructs Hibernate to do everything in one query, whereas entity graphs allow to defer the fetching to secondary statements.
Either way, I would suggest you to look into Blaze-Persistence for pagination which will take care of the pagination related parts.
Wish I could use it but the blaze project since I like the method chaining, but I’m working in a hibernate reactive project and the team I’m part of wants to reduce/remove our double selection query that mitigates HHH000104 warning, with what we gathered an entity graph query instead but there we are running into an issue (Queries using EntityGraph and maxResults don't return all items in joined collection · Issue #1551 · hibernate/hibernate-reactive · GitHub, [HHH-17698] - Hibernate JIRA) where the entity graphs is not fetching all the associated objects in their lists/sets.
But either how the entity graph approach would still result in a HHH000104 warning or just hide it and apply join fetches filters in memory still?
but I’m working in a hibernate reactive project
I hope you made sure you that Hibernate Reactive gives you a performance/throughput boost in your real world scenarios, because most of the time people just add a lot of complexity with Hibernate Reactive for no real gain.
wants to reduce/remove our double selection query that mitigates HHH000104 warning
First, you have to understand why this even happens. Like I wrote before, potentially you are fetching a Collection + a List (Bag) which needs to separate selects to retain result cardinality. A potential solution is to use Set types instead or indexed List with @OrderColumn.
Note though that using join fetching when multiple collection are involved can quickly lead to result set size explosion i.e. fetching 100 entities with two collections and each collection contains on average 10 elements, might result in 10000 rows to be transferred over the network, read from the JDBC result set, which usually is slower than executing 2 or 3 queries. You can use @Fetch(FetchMode.SUBSELECT) on your associations to fetch your all collections of previously read entities with a single query.