0

I have two entities linked by a @ManyToMany association. Let's say Text and Country. So in my Text entity, I have:

@ManyToMany(fetch = FetchType.LAZY)
private Set<Country> countries = new HashSet<>();

I want to perform a paginated query that returns texts, so in a JPA repository, I write:

Page<Text> findAll(Pageable pageable);

This works. The problem is that as countries are lazy loaded, hibernate executes an extra select query for each country to load.

So I can use @EntityGraph to fetch countries:

@EntityGraph("countries")
Page<Text> findAll(Pageable pageable);

It works, I have only one query, but it is wayyyyyyyyyy much longer (about 12x). I seems that it fetches the countries for all the texts (I have a lot of texts) before applying the pagination. This is confirmed by the Spring data team: https://github.com/spring-projects/spring-data-jpa/issues/1976

So is there a way to perform an optimized paginated query, without N+1 select and without loading the entire table ?

Thank you very much

MWiesner
  • 8,868
  • 11
  • 36
  • 70
Clément Picou
  • 4,011
  • 2
  • 15
  • 18
  • Do you need the countries? Problem is you get a cartesian product, which means that instead 50 rows (if that is your page size) you get 50* rows which need to be parsed, until the page size is reached. – M. Deinum Sep 22 '22 at 12:54
  • I understand. But I don't want 50*X rows, I want 50 rows (or texts) with the country list loaded for each. Each text is associated to one or two countries. – Clément Picou Sep 22 '22 at 13:25
  • That is how the database works it will return a row for each country which then needs to be parsed back to a single text. I know what you want I'm just explaining what happens on the DB side of things and what hibernate (assuming that is your JPA provider) needs to do to shoehorn it back into the entity model. But as stated in the linked issue JPA will retrieve everything and do in-memory paging, also in the same issue is a way around it. – M. Deinum Sep 22 '22 at 13:26
  • Yes, thank you. What about doing pagination by hand ? If my JPA query returns a Stream, and then I use skip and limit – Clément Picou Sep 22 '22 at 13:27
  • You have the same issue (more or less) as HIbernate now has, and I suspect it will get progressivly slower when you request later pages. If you have read the issue there is also a link to a solution by writing a SQL query. – M. Deinum Sep 22 '22 at 13:35
  • Other providers have batch reading capabilities on mappings that might help, as it can be configured to query using an IN clause instead of joining or reusing the selection criteria from the paginated query. See https://www.eclipse.org/eclipselink/documentation/2.4/jpa/extensions/a_batchfetch.htm. I don't know that Hibernate has the same. This will result in one extra statement, but this can be more efficient depending on the data, as it reduces the number of rows returned from the main query (no Cartesian product), so both should return much more quickly. – Chris Sep 22 '22 at 13:44

0 Answers0