Is this an N+1 problem and how do I fix it?

Question

I have a list of projects and a list of customers. A project can be for one customer and every customer can have many projects. So it's a simple 1:n relationship where the project is the owning side.

Simplified to the essential it is

@Entity
public class Project {
  @Id
  long id;

  @ManyToOne(optional = true)
  @JoinColumn(name = "customer", nullable = true, updatable = true)
  Customer customer;
}

@Entity
public class Customer {
  @Id
  long id;
}

When I load a list of projects, I want to retrieve the customers efficiently at the same time. This is not the case. There is one single query for the projects and then for every distinct customer that is encountered a separate query is issued.

So say I have 100 projects that are assigned to 50 different customers. This would result in one query for the projects and 50 queries for the customers.

This quickly adds up and for large project/customer lists our application gets rather slow. Also this is just one example. All our entities with relationships are affected by this behavior.

I already tried @Fetch(FetchMode.JOIN) on the customers field as suggested here but it does nothing and FetchMode.SUBQUERY is not applicable according to Hibernate:

org.hibernate.AnnotationException: Use of FetchMode.SUBSELECT not allowed on ToOne associations

How can I fix this problem?

I don't think it's a N+1 problem, the default fetch-type for your `@ManyToOne` relationship is already `FetchType.EAGER` — Raphael Roth, Apr 12 '16 at 10:15

score 2 · Answer 1 · edited May 23 '17 at 11:59

2

Yes, it is a by-the-book example of the n+1 selects problem.

The approach I use in most cases is to make the association lazy and define a batch size.

Alternatively, you could use a JPQL query with [left] join fetch to initialize the association directly from the query result set:

select p from Project p left join fetch p.customer

edited May 23 '17 at 11:59

Community

1
1

answered Apr 12 '16 at 11:57

Dragan Bozanovic

23,102
5
43
110

Is that the only way? I'm asking because I use Spring Data and don't always have a written query. Shouldn't `@Fetch(FetchMode.JOIN)` do the same? – musiKk Apr 12 '16 at 12:10
`FetchMode` has no impact on queries (which Spring Data probably autogenerates under the hood anyway based on the naming conventions in repository methods). PS Spring Data does not prevent you from writing queries. – Dragan Bozanovic Apr 12 '16 at 12:17
That's true but I'm using the `JpaSpecificationExecutor` interface which afaik does not allow one to write the queries. [I can set the fetch mode in a Specification](https://jdpgrailsdev.github.io/blog/2014/09/09/spring_data_hibernate_join.html) but that looks super hacky to me. I basically have to rely on a side effect of the specification to achieve what I want. – musiKk Apr 12 '16 at 12:26
My answer considers pure JPA/Hibernate behavior, you'll have to consult the documentation of any wrappers around it you use. But whichever they are, they have no choice but to delegate to the underlying ORM, so the basic concepts are the same. IMHO it is highly inflexible and limited if you use an ORM wrapper that doesn't allow you to write custom JPA queries. – Dragan Bozanovic Apr 12 '16 at 13:31
True. But even then sometimes I have many different queries for an entity and have to redundantly add many join fetches to many queries. And I cannot even join fetch transitive relationships. If that's really the only way then JPA is a bit limited itself. – musiKk Apr 13 '16 at 06:42

score 2 · Answer 2 · answered Nov 10 '20 at 17:07

If you are using Spring Data JPA to implement your repositories, you can specify lazy fetching in the JPA entities:

@Entity
public class Project {
  @Id
  long id;

  @ManyToOne(fetch = FetchType.LAZY, optional = true)
  @JoinColumn(name = "customer", nullable = true, updatable = true)
  Customer customer;
}

@Entity
public class Customer {
  @Id
  long id;
...
}

And add @EntityGraph to your Spring Data JPA-based repository:

@Repository
public interface ProjectDao extends JpaRepository<Project, Long> {

    @EntityGraph(
            type = EntityGraphType.FETCH,
            attributePaths = { 
                    "customer" 
            }
    )
    Optional<Project> findById(Long id);
...
}

My blog post at https://tech.asimio.net/2020/11/06/Preventing-N-plus-1-select-problem-using-Spring-Data-JPA-EntityGraph.html helps you preventing the N+1 select problem using Spring Data JPA and @EntityGraph.

Thank you sir for sharing! This worked for me :) – Fabio Delarias May 30 '21 at 16:38 — Fabio Delarias, May 30 '21 at 16:38

score 0 · Answer 3 · answered Apr 30 '19 at 17:48

Yes, it is a by-the-book example of the n+1 selects problem as @dragan-bozanovic said.

In Spring-Boot 2.1.3 @Fetch(FetchMode.JOIN) can be used to solve it:

  @ManyToOne(optional = true)
  @Fetch(FetchMode.JOIN)
  @JoinColumn(name = "customer", nullable = true, updatable = true)
  Customer customer;

Warning: If the relationship can be invalid, for example when marked with @NotFound(action = NotFoundAction.IGNORE), each invalid relationship will trigger another SELECT query.

Is this an N+1 problem and how do I fix it?

3 Answers3