0

I have a database with a quite large entity model. In total, there are 14 tables with around 100k records on average.

When I tested my application that takes one entity from the database and converts it to json and throws that back to the caller, it took 7 seconds to get the entry.

This doesnt seem an initialization issue because if I do the same call twice in a row, they both take around 10 seconds to get the data.

When I enable sql script logging, I find that for each entity read, hibernate sends hundreds of sql requests to the database. (The actual number is based on how many connections/licenses/products/services etc the entity has but for my test entry, I got 286 queries (which took around 7 seconds in total)) When the database is empty (except for the data that the test should return), then it takes around 6 seconds.

I suppose the issue is because I have my @OneToMany's and @ManyToMany's fetch set to Lazy, but when I set them to eager, I get the error of multiple fetch bags.

@javax.persistence.OneToMany(fetch = javax.persistence.FetchType.LAZY)
@org.hibernate.annotations.LazyCollection(org.hibernate.annotations.LazyCollectionOption.FALSE)
@javax.persistence.JoinColumn(name = "ProductId")

This is an example of a OneToMany relation I have, which simulates the eager fetch without the multiple fetch bags error.

Is this a common issue? And how do I improve the speed on the reading?

Wietlol
  • 1,001
  • 1
  • 10
  • 25
  • Just how large is your JSON document and how many relations are there? Fetch type should typically be lazy, but if you need to read the world that will always be slow. If a single entity in otherwise empty tables takes 6 seconds it must be huge! – ewramner Apr 25 '18 at 10:49
  • the resulting json is 2652 lines long formed by a total of 502 records from the database – Wietlol Apr 25 '18 at 11:00
  • Remove the Hibernate-specific LazyCollection annotation, you get the same effect with the portable FetchType.EAGER (which I don't recommend in general). Then see how many queries there are? Do you make 502 selects, or are there a few selects with many results? – ewramner Apr 25 '18 at 11:14
  • if i set the fetch type to eager, i get a MultipleBagFetchException, the LazyCollection annotation was the solution to that i am not sure what other solutions exist – Wietlol Apr 25 '18 at 11:22
  • I don't want to use eager fetching, I'd suggest using a join fetch or a fetch graph as per the answer below or changing the batch size for reading. However, in order to get that far I'd like to know how many selects there are when the system uses lazy fetching and for how many tables? – ewramner Apr 25 '18 at 11:44
  • Are you using Lists for the eager fetched collections? If so, try using Sets instead. – Gimby Apr 25 '18 at 12:03
  • If I just use lazy fetching, I get an org.hibernate.LazyInitializationException: could not initialize proxy - no Session exception... I am using Collection everywhere, I could change this to Set, but afaik, it will use Set anyway – Wietlol Apr 25 '18 at 12:10
  • @Wietlol the results you're getting are not really backing that up. The documentation is vague but from what I can tell Hibernate will treat a Collection like a bag type. In any case, you can pre-fetch specific lazy collections by fetching the data with a JPQL query and then using a [JOIN FETCH construct](https://stackoverflow.com/questions/17431312/difference-between-join-and-join-fetch-in-hibernate). – Gimby Apr 26 '18 at 09:49

1 Answers1

0

Hibernate does not allow to have more then one collection of type List marked as EAGER.

The best to load the data eager is to use a JOIN FETCH

select e from YourEntity JOIN FETCH e.yourList l

Another option could be to use an EntityGraph to define the EAGER loading.

Read more about that topic here:

https://vladmihalcea.com/hibernate-facts-the-importance-of-fetch-strategy/

If you use Spring Data JPA repository interfaces you can use NamedEntityGraphs:

https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.entity-graph

Simon Martinelli
  • 34,053
  • 5
  • 48
  • 82
  • I am not using queries with JPQL though, I am using a org.springframework.data.jpa.repository.JpaRepository which makes queries based on the method names of the interface. I prefer to keep the repository approach because of how easy it is to mock a database with it – Wietlol Apr 25 '18 at 12:12
  • Then I would recommend to use EntityGraphs. See my updated answer. – Simon Martinelli Apr 25 '18 at 12:15
  • As far as I can see, it only pulls queries earlier into the process, but it does not decrease the number of queries done. – Wietlol Apr 25 '18 at 13:07
  • You have the classical n+1 read problem. There are several ways to solve that: https://stackoverflow.com/questions/97197/what-is-the-n1-select-query-issue?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa – Simon Martinelli Apr 25 '18 at 13:24