# Efficient Queries Avoiding Massive Joins with Broad Class Hierarchy

**URL:** <https://discourse.hibernate.org/t/efficient-queries-avoiding-massive-joins-with-broad-class-hierarchy/1254>\
**Category:** Hibernate ORM\
**Created:** [August 17, 2018, 10:12am UTC](https://discourse.hibernate.org/t/efficient-queries-avoiding-massive-joins-with-broad-class-hierarchy/1254 "2018-08-17T10:12:28Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![j4r](https://avatars.discourse-cdn.com/v4/letter/j/ecae2f/32.png) [@j4r](https://discourse.hibernate.org/u/j4r)\
**Post date:** [August 17, 2018, 10:12am UTC](https://discourse.hibernate.org/t/efficient-queries-avoiding-massive-joins-with-broad-class-hierarchy/1254/1 "2018-08-17T10:12:28Z")

</div>

I have a relatively large class hierarchy in terms of width, so many classes inheriting from one root class. The maximum depth of the hierarchy is only 4 levels when counting from the root to the leaves of the hierarchy tree. Let’s call the root class `RootClass` and the leaves `Lx` (== `L1, L2, ..., L20`). I only store objects of type `Lx` in the database, never of `RootClass` or an intermediate type.

The `Lx`es are referred to by various other classes but only stored as `RootClass`es there. The ORM is as follows: `RootClass` has a table annotated with a _single table inheritance_ strategy. As storing all `Lx`'s varying properties in this `RootClass` table is extremely inefficient, I have _secondary tables_ for each `Lx` that contains additional properties and theoretically I can determine the actual type of an object - and hence table - by the discriminator value in the `RootClass`'s table. My reasoning is here that Hibernate should be able to efficiently query a stored object, as it can infer which two tables to join from the discriminator value.

**The problem** : The `RootClass` contains a many-to-many relation to (a Set of) other `RootClass` objects. When I try to fetch that set to iterate over its content hibernate produces a query with over 80 joins. This causes a `java.sql.SQLException: Too many tables`.

I assume hibernate is trying to fetch the data I require with a single statement and the only way to do this is such a massive join. Is there a way to get hibernate to fetch the data more intelligently by using the discriminator values and several queries? Is my ORM bad and a way to fix this issue by improving the ORM? I have tried a lot of mappings for this class hierarchy and haven’t come up with anything better so far.

Thanks for any tips! 🙂

---

<div class="post-metadata">

**Author:** ![vlad](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vlad/32/658_2.png) [@vlad](https://discourse.hibernate.org/u/vlad)\
**Post date:** [August 17, 2018, 10:31am UTC](https://discourse.hibernate.org/t/efficient-queries-avoiding-massive-joins-with-broad-class-hierarchy/1254/2 "2018-08-17T10:31:13Z")

</div>

> **The problem** : The `RootClass` contains a many-to-many relation to (a Set of) other `RootClass` objects. When I try to fetch that set to iterate over its content hibernate produces a query with over 80 joins. This causes a `java.sql.SQLException: Too many tables`.

**The solution** : Replace the `Set` with a query. A query is much more flexible than an association. You can use the `treat` operator or you could break it into multiple queries against a limited number of subclasses.

---

<div class="post-metadata">

**Author:** ![j4r](https://avatars.discourse-cdn.com/v4/letter/j/ecae2f/32.png) [@j4r](https://discourse.hibernate.org/u/j4r)\
**Post date:** [August 17, 2018, 12:34pm UTC](https://discourse.hibernate.org/t/efficient-queries-avoiding-massive-joins-with-broad-class-hierarchy/1254/3 "2018-08-17T12:34:30Z")

</div>

Thank you, that sounds promising! So would I effectively mark the `Set` as transient and write my own queries to fetch/store the objects when I need them by executing them instead of using `getMySet()`? Or is there a way to integrate it in a more automatic manner with hibernate, also including the whole lazy fetching functionality hibernate uses anyway (I imagine defining a query somewhere which hibernate will then use when I call `getMySet()`)? I did not really find much on ways to go when replacing associations or collections with queries.

---

<div class="post-metadata">

**Author:** ![vlad](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vlad/32/658_2.png) [@vlad](https://discourse.hibernate.org/u/vlad)\
**Post date:** [August 17, 2018, 12:48pm UTC](https://discourse.hibernate.org/t/efficient-queries-avoiding-massive-joins-with-broad-class-hierarchy/1254/4 "2018-08-17T12:48:51Z")

</div>

Nope. Just use a query like `entityManager.createQuery`. You don’t need to map everything with associations.

---

<div class="post-metadata">

**Author:** ![j4r](https://avatars.discourse-cdn.com/v4/letter/j/ecae2f/32.png) [@j4r](https://discourse.hibernate.org/u/j4r)\
**Post date:** [August 17, 2018, 1:11pm UTC](https://discourse.hibernate.org/t/efficient-queries-avoiding-massive-joins-with-broad-class-hierarchy/1254/5 "2018-08-17T13:11:19Z")

</div>

How do I now elegantly tell Hibernate to create the join table in the first place? As this association is not part of the mapping any more it does not do so automatically and randomly executing a `CREATE TABLE` command seems a little odd to me at first glance.

---

<div class="post-metadata">

**Author:** ![vlad](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vlad/32/658_2.png) [@vlad](https://discourse.hibernate.org/u/vlad)\
**Post date:** [August 17, 2018, 1:22pm UTC](https://discourse.hibernate.org/t/efficient-queries-avoiding-massive-joins-with-broad-class-hierarchy/1254/6 "2018-08-17T13:22:40Z")

</div>

Just like the [Hibernate User Guide](http://docs.jboss.org/hibernate/orm/5.3/userguide/html_single/Hibernate_User_Guide.html#best-practices-schema) tells you, it’s much better if the schema is handled by an automatic migration script tool:

> Although Hibernate provides the `update` option for the `hibernate.hbm2ddl.auto` configuration property, this feature is not suitable for a production environment.
> 
> An automated schema migration tool (e.g. [Flyway](https://flywaydb.org/), [Liquibase](http://www.liquibase.org/)) allows you to use any database-specific DDL feature (e.g. Rules, Triggers, Partitioned Tables). Every migration should have an associated script, which is stored on the Version Control System, along with the application source code.

Relying on `hbm2ddl` for that is only useful for the very first initial script or for running integration tests when developing the Hibernate ORM.

---

<div class="post-metadata">

**Author:** ![j4r](https://avatars.discourse-cdn.com/v4/letter/j/ecae2f/32.png) [@j4r](https://discourse.hibernate.org/u/j4r)\
**Post date:** [August 20, 2018, 12:49pm UTC](https://discourse.hibernate.org/t/efficient-queries-avoiding-massive-joins-with-broad-class-hierarchy/1254/7 "2018-08-20T12:49:04Z")

</div>

> [@vlad](#):
>
> Relying on `hbm2ddl` for that is only useful for the very first initial script or for running integration tests when developing the Hibernate ORM.

Okay, I had hoped to be able to set up the database more or less effortlessly for testing but using a script there as well won’t be an issue.

Please excuse my repeated queries, perhaps I am missing something fundamental here but I am still not clear on everything.

The problem is larger than originally thought, I simply happened to notice it first in aforementioned situation and have only realised what the issue is in its entirety now. I have various objects referring to instances of the `Lx` classes, including but not limited to some `Lx`-typed objects themselves. Every time one of those referenced objects is lazily loaded a huge query with far too many joins is produced.

Is the way to go here to create native queries for all of these cases? So effectively not using Hibernate, its ORM and lazy loading with proxies in these situations? Two advantages of Hibernate for our project are not having to specify as many queries manually and the ability to plug in different back-end database systems – but by using native queries we would surely sacrifice that? (Native queries because we need to be able to access unmapped tables for associations).

What am I missing here? 🤔

---

<div class="post-metadata">

**Author:** ![vlad](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vlad/32/658_2.png) [@vlad](https://discourse.hibernate.org/u/vlad)\
**Post date:** [August 20, 2018, 1:16pm UTC](https://discourse.hibernate.org/t/efficient-queries-avoiding-massive-joins-with-broad-class-hierarchy/1254/8 "2018-08-20T13:16:00Z")

</div>

Maybe you are using `@ManyToOne` or `@OneToOne` which by default ate fetched eagerly.

Switch to using FetchType.LAZY. For more details, check out [this article](https://vladmihalcea.com/eager-fetching-is-a-code-smell/).

---

<div class="post-metadata">

**Author:** ![j4r](https://avatars.discourse-cdn.com/v4/letter/j/ecae2f/32.png) [@j4r](https://discourse.hibernate.org/u/j4r)\
**Post date:** [August 20, 2018, 2:04pm UTC](https://discourse.hibernate.org/t/efficient-queries-avoiding-massive-joins-with-broad-class-hierarchy/1254/9 "2018-08-20T14:04:46Z")

</div>

> [@vlad](#):
>
> Switch to using FetchType.LAZY

Unfortunately this only shifts the issue to the moment at which I try to access the property in question. As soon as Hibernate goes out to fetch the required data lazily it suffers the same gigantic-join-problem. This even occurs when simply performing a regular query with e.g. `session.get(RootClass.class, 123)`, the result is always a massive amount of joins.

---

<div class="post-metadata">

**Author:** ![vlad](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vlad/32/658_2.png) [@vlad](https://discourse.hibernate.org/u/vlad)\
**Post date:** [August 20, 2018, 3:19pm UTC](https://discourse.hibernate.org/t/efficient-queries-avoiding-massive-joins-with-broad-class-hierarchy/1254/10 "2018-08-20T15:19:57Z")

</div>

If you are using lazy loading, no association will be fetched unless you need it.

If you have a gigantic inheritance structure that you have to fetch at once, you could use 2nd-level cache to avoid the joins or reconsider the design/model since you might not really need all that data for every use case.

---

<div class="post-metadata">

**Author:** ![j4r](https://avatars.discourse-cdn.com/v4/letter/j/ecae2f/32.png) [@j4r](https://discourse.hibernate.org/u/j4r)\
**Post date:** [August 28, 2018, 9:35am UTC](https://discourse.hibernate.org/t/efficient-queries-avoiding-massive-joins-with-broad-class-hierarchy/1254/11 "2018-08-28T09:35:53Z")

</div>

> [@vlad](#):
>
> Maybe you are using `@ManyToOne` or `@OneToOne` which by default ate fetched eagerly.
> 
> Switch to using FetchType.LAZY. For more details, check out [this article](https://vladmihalcea.com/eager-fetching-is-a-code-smell/).

This. The associations each added joins to the queries when fetched eagerly - I just wasn’t aware of that connection and was thinking in a totally different direction with my next post. Thanks, loading XToOne associations lazily solved it :).