Hibernate creating N+1 queries for @ManyToOne JPA annotated property

Question

I have these classes:

@Entity
public class Invoice implements Serializable {
    @Id
    @Basic(optional = false)
    private Integer number;

    private BigDecimal value;

    //Getters and setters
}

@Entity
public class InvoiceItem implements Serializable {
    @EmbeddedId
    protected InvoiceItemPK invoiceItemPk;

    @ManyToOne
    @JoinColumn(name = "invoice_number", insertable = false, updatable = false)
    private Invoice invoice;

    //Getters and setters
}

When i run this query:

session.createQuery("select i from InvoiceItem i").list();

It executes one query to select the records from InvoiceItem, and if I have 10000 invoice items, it generates 10000 additional queries to select the Invoice from each InvoiceItem.

I think it would be a lot better if all the records could be fetched in a single sql. Actually, I find it weird why it is not the default behavior.

So, how can I do it?

Bridoon

Answer 1

Try with

session.createQuery("select i from InvoiceItem i join fetch i.invoice inv").list();

It should get all the data in a single SQL query by using joins.

Basalt

Well actually it works fine, but is there a way to do it using annotations? Because let's say we have a lot of @ManyToOne annotated fields, i don't want to write a lot of JOIN FETCH XXX just to avoid the problem... – Bridoon

Hibernate has a @Fetch annotation that you can use, put @Fetch(FetchMode.JOIN) on your relation mapping. – Basalt

Even using @Fetch(FetchMode.JOIN) it still creates additional queries. Guess i'll have to work with the query fix. – Bridoon

Maybe @Fetch is ignored in queries, but is used if you get the entity using session.get(), you might check if that's true. – Basalt

@PredragMaric Yes, it is ignored - see also https://mcmap.net/q/176109/-why-hibernate-sometimes-ignores-fetchmode-join – Rowles

Does not work for me. QueryException: query specified join fetching, but the owner of the fetched association was not present in the select list – Symbiosis

Answer 2

The problem here is not related to Hibernate but to JPA.

Prior to JPA 1.0, Hibernate 3 used lazy loading for all associations.

However, the JPA 1.0 specification uses FetchType.LAZY only for collection associations:

• @OneToMany,
• @ManyToMany
• @ElementCollection)

The @ManyToOne and @OneToOne associations use FetchType.EAGER by default, and that's very bad from a performance perspective.

The behavior described here is called the [N+1 query issue][5], and it happens because Hibernate needs to make sure that the @ManyToOne association is initialized prior to returning the result to the user.

Now, if you are using direct fetching via entityManager.find, Hibernate can use a LEFT JOIN to initialize the FetchTYpe.EAGER associations.

However, when executing a query that does not explicitly use a JOIN FETCH clause, Hibernate will not use a JOIN to fetch the FetchTYpe.EAGER associations, as it cannot alter the query that you already specified how to be constructed. So, it can only use secondary queries.

The fix is simple. Just use FetchType.LAZY for all associations:

   @ManyToOne(fetch = FetchType.LAZY)
   @JoinColumn(name = "invoice_number", insertable = false, updatable = false)
   private Invoice invoice;

More, you should use the Hypersistence Utils to assert the number of statements executed by JPA and Hibernate.

Bellyful

"However, when executing a query that does not explicitly use a JOIN FETCH clause, Hibernate will not use a JOIN to fetch the FetchTYpe.EAGER associations, as it cannot alter the query that you already specified how to be constructed. So, it can only use secondary queries." From my recent testing, I have seen this is not applicable to Criteria queries. Is that true? – Ashley

Do you have a replicating test case which shows that? – Bellyful

Its a bit hard to create a test project for this due to the complexity of how I am currently bootstrapping but I will try and remember to make a project which shows this when I get chance. The basic idea is when I select an entity which has an @ManyToOne relationship, in the Critieria query case I see a single SQL query being executed (and the related entity is joined to). In the JPQL case, I see two SQL queries being executed, the second of which selects the related entity. I wasn't using your tool to count queries, just looking at SQL output (hibernate.show_sql = true). – Ashley

Can you please look at this problem ? #59850008 – Caeoma

A note as it occurred to me: FetchType.LAZY won't make any difference in the number of SQL requests if after your query you access data from the joined table. In that case a JOIN FETCH is necessary. – Frolic

Answer 3

Yes there is setting you need: @BatchSize(size=25). Check it here:

20.1.5. Using batch fetching

small cite:

Using batch fetching, Hibernate can load several uninitialized proxies if one proxy is accessed. Batch fetching is an optimization of the lazy select fetching strategy. There are two ways you can configure batch fetching: on the class level and the collection level.

Batch fetching for classes/entities is easier to understand. Consider the following example: at runtime you have 25 Cat instances loaded in a Session, and each Cat has a reference to its owner, a Person. The Person class is mapped with a proxy, lazy="true". If you now iterate through all cats and call getOwner() on each, Hibernate will, by default, execute 25 SELECT statements to retrieve the proxied owners. You can tune this behavior by specifying a batch-size in the mapping of Person:

<class name="Person" batch-size="10">...</class>

With this batch-size specified, Hibernate will now execute queries on demand when need to access the uninitialized proxy, as above, but the difference is that instead of querying the exactly proxy entity that being accessed, it will query more Person's owner at once, so, when accessing other person's owner, it may already been initialized by this batch fetch with only a few ( much less than 25) queries will be executed.

So, we can use that annotation on both:

• collections/sets
• classes/Entities

Check it also here:

• @BatchSize but many round trip in @ManyToOne case

Aphasic

Answer 4

In this Method there are Multiple SQLs fired. This first one is fired for retrieving all the records in the Parent table. The remaining are fired for retrieving records for each Parent Record. The first query retrieves M records from database, in this case M Parent records. For each Parent a new query retrieves Child.

Beasley