0

I am facing with 2 problems: N + 1 query and Out Of Memory (OOM).

I solved OOM by paging and lazy loading:

@OneToMany(fetch = FetchType.LAZY)
@JoinColumn(name = "department_id")
private Set<Employee> employees;

But when I use lazy loading, N + 1 query happened. So I try to use EntityGraph as https://www.baeldung.com/spring-data-jpa-named-entity-graphs. But as my researches and local test, EntityGraph always do eager loading for NamedAttributeNode field - association field, which I want to be lazy loading - do not load all data at first:

@Entity
@Table(name = "department")
@NamedEntityGraph(name = "Department",
        attributeNodes = {
                @NamedAttributeNode("employees")
        }
)
public class Department implements Serializable {
    @OneToMany(fetch = FetchType.LAZY)
    @JoinColumn(name = "department_id")
    private Set<Employee> employees;
}

So are there any way to get them both ? Use EntityGraph to avoid N + 1 and lazy loading to avoid OOM ?

UPDATE: Can EntityGraph works fine with Pageable effectively ? I mean do not load all data in JOIN query.

Thach Huynh
  • 1,173
  • 2
  • 12
  • 24

2 Answers2

2

Using EntityGraph all your NamedAttributeNode associations will be loaded in 1 query with Join clause. Enable sql log to see how many queries hibernate does for loading entities in different scenarios

logging.level.org.hibernate.SQL=DEBUG

You will see that using @OneToMany(fetch = FetchType.EAGER) without EntityGraph it loads employees in separate select queries (N + 1), but using EntityGraph it performs only 1 select ... join

Also don't forget to specify entity graph name in repository like:

@EntityGraph(value = "Department")
List<Department> findAll();

UPDATE: Spring DATA Pagination doesn't work on database side. It will fetch all data and then filtered in memory. That's how it works.. There are some workarounds, check this links:

How can I avoid the Warning "firstResult/maxResults specified with collection fetch; applying in memory!" when using Hibernate?

Avoiding "HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!" using Spring Data

VladMihalcea Blog The best way to fix the Hibernate HHH000104

As for me the solution could be creating custom repository and using EntityManager to construct query manually.

Ruslan
  • 6,090
  • 1
  • 21
  • 36
  • "You will see that using @OneToMany(fetch = FetchType.EAGER) without EntityGraph it loads employees in separate select queries (N + 1), but using EntityGraph it performs only 1 select ... join" -> this is what I mentioned above: it will load Employees EAGERLY but I want it to be LAZY – Thach Huynh Feb 07 '20 at 08:56
  • @ThachHuynh Ok, then why do you need `EntityGraph`? Just don't use `@NamedAttributeNode` for lazy employees. In that case employees will not be loaded at all. – Ruslan Feb 07 '20 at 09:14
  • I use EntityGraph because it can avoid N + 1 select problem. – Thach Huynh Feb 07 '20 at 09:17
  • Are there any other ways ? – Thach Huynh Feb 07 '20 at 09:20
  • @ThachHuynh so you mean you want to load employees but limit the number of it according to the pagination? – Ruslan Feb 07 '20 at 09:30
  • Yes. Pagination or lazy load is ok. – Thach Huynh Feb 07 '20 at 09:43
  • But I'm not sure that spring data jpa pagination is real pagination. I mean it must not load all data to memory and do paging on it. It must come to db and execute query every batch. – Thach Huynh Feb 07 '20 at 09:45
  • 1
    @ThachHuynh I've updated answer. Probably you could find smth useful there – Ruslan Feb 07 '20 at 10:51
  • "I'm not sure that spring data jpa pagination is real pagination" -> This is not true. Spring Data JPA _will_ apply the pagination at the database level. – Wim Deblauwe May 25 '20 at 09:15
0

A simple solution is:

  • Fetch first your records with pagination but without @EntityGraph.
  • Fetch your records with @EntityGraph from previous returned ids.

=> No N+1 complexity, only 2 SQL requests and no native queries, etc.

Example:

On your main repository, add @EntityGraph annotation with associations you want to fetch:

public interface CarRepository extends JpaRepository<Car, String>, JpaSpecificationExecutor<Car>, CarCustomRepository {

    @Override
    @EntityGraph(attributePaths = { "foo", "bar", "bar.baz" })
    List<Car> findAllById(Iterable<String> ids);

}

Create a custom repository with a findAllWithEntityGraph method:

public interface CarCustomRepository {

    Page<Car> findAllWithEntityGraph(Specification<Car> specification, Pageable pageable);

}

The implementation of the custom repository. It first fetch entities without entity graph, then it re-fetch them with entity graph in order to load associations. Don't forget to re-sort entities in to preserve order:

public class CarCustomRepositoryImpl implements CarCustomRepository {

    @Autowired
    @Lazy
    private CarRepository carRepository;

    @Override
    public Page<Car> findAllWithEntityGraph(Specification<Car> specification, Pageable pageable) {
        Page<Car> page = carRepository.findAll(specification, pageable);
        List<String> ids = page.getContent().stream().map(Car::getId).collect(Collectors.toList());
        List<Car> cars = carRepository.findAllById(ids).stream()
                .sorted(Comparator.comparing(car -> ids.indexOf(car.getId())))
                .collect(Collectors.toList());
        return new PageImpl<>(cars, pageable, page.getTotalElements());
    }

}

Then, you just have to invoke CarRepository#findAllWithEntityGraph method in order to fetch records with pagination and no N+1 complexity.

The question is: why hibernate does not have this behavior by default?

Alexis Toulotte
  • 1