Hibernate generate m + 1 queries in Many-to-one

Question

My problem is very simple but I don't know how to make Hibernate does the way I want: - table MainTable have Many-2-One with ParentTable (with 100 rows). MainTable point to m=26 rows out of 100 rows in ParentTable

@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "PARENT_ID")
@Fetch(FetchMode.JOIN)

When I simply query "from MainTable"

it will generate 26 + 1 queries

When I trace the queries, the 1st query only load PARENT_ID used by 26 later queries. I guess it should have the way to load the whole PARENT_TABLE in the 1st query..

Please help by assuming that:

FetchType.EAGER is a MUST
Using from MainTable mt left join fetch mt.parent parent is ok but we have many association

Avoiding N+1 selects problem: http://www.realsolve.co.uk/site/tech/hib-tip-pitfall.php?name=n1selects http://stackoverflow.com/questions/97197/what-is-the-n1-selects-problem — Aravind Yarram, Aug 07 '12 at 18:08
@Pangea : Thanks but here some comments: 1) Using fetch join is good, but we have about ten Parent table. That will be the last solution 2) We want something similar to Subselect fetch in One-2-Many, so that 1 query for MainTable, 1 more for related table — Phung D. An, Aug 08 '12 at 00:30
@Fetch(FetchMode.JOIN) and @Fetch(FetchMode.SELECT) give no diff at all !!! o_0 — Phung D. An, Aug 08 '12 at 00:59
I have never got eager fetch working in my previous projects :P it works in some case but causing more problem in other, especial for XxxToMany relationship. I remember in Hibernate's document, it has talked about in which situation eager fetch will/will not work. I would recommend making all of them lazy, and do eager fetch explicitly using join fetch . — Adrian Shum, Aug 08 '12 at 02:24

score 4 · Answer 1 · edited May 23 '17 at 10:32

 // Annotate ParentTable Persistance class file with a batch Size 
 @BatchSize(size=100)
 class ParentTable{
     ..
 }

@ManyToOne
@JoinColumn(name = "PARENT_ID")

This will reduce the number of queries by n/100+1.

The reason for this issue is that, the hibernate will fetch the data in lazy mode internally, (I am not talking about the FetchMode.Lazy). The Lazy mode can be excluded by using FetchMode.SUBSELECT, which is applicable only for collections. When it comes to @ManyToOne, you can select a batch of data, by specifying the batchSize.

A short Description about Fetch Startegies

FetchMode.SUBSELECT

One Query for parent, One Query for the Related table. Applicable only for Collections framework. Only 2 queries fired.

FetchMode.SELECT

One Query For Parent, N Queries for the Child.

FetchMode.JOIN

One Query for Parent, N Queries for Child, but the database retrieval take place upfront in JOIN.

FetchType.Batch

One Query for parent and n/batchSize + 1 number of queries fired.

There are two type of fetching, based on when the queries should be executed.

FetchType.EAGER :

The queries are fired instantly.

FetchType.LAZY :

The queries are fired when the child object is accessed. So number of of queries executed will depent on the number of child objects accessed.

How the Fetch Strategies work is better explained here.

Hibernate generate m + 1 queries in Many-to-one

1 Answers1