0

I'm currently trying to figure out an efficient way to fetch @OneToMany mapped entities from a list of other entities.

Let's have a look into the details:
I have the two classes E and L (Note: both have the annotations, etc. necessary for JPA/Hibernate. I'm just omitting these information since they're not important for my case)

public class L {
    public String name;
}

public class E {
    @ManyToMany(cascade = { CascadeType.PERSIST, CascadeType.MERGE })
    public List<L> ls = new LinkedList<>();

    public String name;

    [...]

}

for an export I want to get all E objects and their relevant information including the respective Lists of L. Usually I'd do it with a normal Query like the following:

@Query("select e from E e where [...]")

The result will be List<E>

when I go on and create my export the system fetches for each E the list of L, which sums up if we're talking about 1000s of entries.

In my current test system I'm retrieving 3982 rows which takes 5323ms in total. compared to 338ms if i just get the E entities without fetching the L entities.

Is there any efficient way to load all information in one go? One attempt I tried is the following query:

@Query("select e, l from E e LEFT JOIN e.ls l where [...]")

which returns List<Object[]> Using this query the amount of rows will increase, since some E might be duplicate when they contain more than 1 L.

But maybe there is a better way that I'm not aware of?

Mark Rotteveel
  • 82,132
  • 136
  • 114
  • 158
tagtraeumer
  • 1,277
  • 10
  • 18
  • 1
    Possible duplicate of [What is the solution for the N+1 issue in JPA and Hibernate?](https://stackoverflow.com/questions/32453989/what-is-the-solution-for-the-n1-issue-in-jpa-and-hibernate) – crizzis Jul 02 '19 at 14:39
  • I suggest to use @NamedEntityGraph to avoid n+1 queries problem. Just create the graph definition on your entity and then annotate your query with @EntityGraph(TheNameEntityGraphName) annotation. It will load your entity and the defined subentities gently. – zlaval Jul 02 '19 at 14:44
  • Also you can think if you really need this 3982 rows that you additionally get. Maybe you can filter better with the where clause. If you need to show this info to the user you can consider paging. – Level_Up Jul 02 '19 at 14:59
  • 1
    You don't need to get `l` specifically, it will be included in the result. Use `@Query("select distinct e from E e LEFT JOIN FETCH e.ls where [...]")` to prefetch. Also note the distinct. – K.Nicholas Jul 03 '19 at 01:28
  • Thanks for the comments. i wasn't able to test these yet but i will do as soon as my project allows me. – tagtraeumer Jul 04 '19 at 11:13

1 Answers1

0

As a first attempt you could try two things:

  1. Add the @BatchSize annotation to the List ls
  2. Ensure that you have indexes on the join column(s) and their references

Failing that you could look at populating the lists yourself:

  1. Query all the Es you are after
  2. Extract all the E ids
  3. Partition the ids into batch sizes that are within the in limits of your database
  4. Query all the Ls in each partition for all partitions and add them to the their respective E instance
Marc G. Smith
  • 836
  • 5
  • 8