2

I am using Spring Boot and Hibernate.

Some complex logic, dictated by business, needs to use various nested fields, which traverse various DB relationships (again, some are NxN, Nx1, 1xN, 1x1).

I encountered the N+1 problem, and I solved it at first with HQL, but some queries need several joins and the result sets become unmanageable.

I started working on a custom utility that collects the ids of things that need to be fetched, fetches them all at once and uses the setters to then populate the fields on the starting objects. This utility works for ManyToOne relationships, but is still inefficient with ManyToMany relationships, because it falls back in the N+1 problem when I collect the ids (as it queries the join table once per object via the getter).

How can I solve this? Has this problem really not been solved yet? Am I missing some obvious settings that solves this automagically?

EDIT: I made a toy example with some commentary: https://github.com/marcotama/n-1-queries-example

marcotama
  • 1,991
  • 2
  • 19
  • 24
  • You should read "Spring boot persistence Best Practice (Anghel Leonard)". This book consist of very good recommendation about hibernate tuning. I got to know a lot of pitfalls. – roma2341 Dec 16 '21 at 18:33
  • Could you post an example of SQL query you're trying to execute? That way it would be easier to come up with a solution (just like [fixing `MultipleBagFetchException`](https://vladmihalcea.com/hibernate-multiplebagfetchexception/)). – Stefan Golubović Dec 16 '21 at 22:48
  • @StefanGolubović I added an example – marcotama Dec 17 '21 at 11:21

2 Answers2

0

I had faced the same situation and I had 3 ways to solve it;

  1. increase the fetchsize for the dependent attribute so that the queries are executed in batch
  2. write a custom query for the purpose
  3. define entity graph relations and map accordingly to attributes

I personally preferred the 3rd option as it was convenient to do that and was cleaner with spring data JPA.

you can refer to examples from the comments from the below answers:

srp321
  • 116
  • 2
  • 10
0

Write fetch logic on your own. E.g You have author which has book, author_devices You can join fetch author with books. Than you can separatly fetch author_devices using repository "where author_id IN (authorsList.stream().map(author.getId())". Than you should detach author and iterate author_devices and assign it to apropriate author devices list. I think it's only adequate solution for situations where you need to join-fetch more than 1 relation.

roma2341
  • 111
  • 2
  • 10