Hibernate: fetching multiple bags efficiently
- by Jens Jansson
Hi!
I'm developing a multilingual application. For this reason many objects have in their name and description fields collections of something I call LocalizedStrings instead of plain strings. Every LocalizedString is basically a pair of a locale and a string localized to that locale.
Let's take an example an entity, let's say a book -object.
public class Book{
@OneToMany
private List<LocalizedString> names;
@OneToMany
private List<LocalizedString> description;
//and so on...
}
When a user asks for a list of books, it does a query to get all the books, fetches the name and description of every book in the locale the user has selected to run the app in, and displays it back to the user.
This works but it is a major performance issue. For the moment hibernate makes one query to fetch all the books, and after that it goes through every single object and asks hibernate for the localized strings for that specific object, resulting in a "n+1 select problem". Fetching a list of 50 entities produces about 6000 rows of sql commands in my server log.
I tried making the collections eager but that lead me to the "cannot simultaneously fetch multiple bags"-issue.
Then I tried setting the fetch strategy on the collections to subselect, hoping that it would do one query for all books, and after that do one query that fetches all LocalizedStrings for all the books. Subselects didn't work in this case how i would have hoped and it basically just did exactly the same as my first case.
I'm starting to run out of ideas on how to optimize this.
So in short, what fetching strategy alternatives are there when you are fetching a collection and every element in that collection has one or multiple collections in itself, which has to be fetch simultaneously.
