Fetching multiple bags efficiently
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 wo开发者_Python百科uld 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.
You said
I tried setting the fetch strategy on the collections to subselect, hoping that it would do one query for all books
You can, but you need to access some property to throw the subselect
@Entity
public class Book{
private List<LocalizedString> nameList = new ArrayList<LocalizedString>();
@OneToMany(cascade=javax.persistence.CascadeType.ALL)
@org.hibernate.annotations.Fetch(org.hibernate.annotations.FetchMode.SUBSELECT)
public List<LocalizedString> getNameList() {
return this.nameList;
}
private List<LocalizedString> descriptionList = new ArrayList<LocalizedString>();
@OneToMany(cascade=javax.persistence.CascadeType.ALL)
@org.hibernate.annotations.Fetch(org.hibernate.annotations.FetchMode.SUBSELECT)
private List<LocalizedString> getDescriptionList() {
return this.descriptionList;
}
}
Do as follows
public class BookRepository implements Repository {
public List<Book> getAll(BookFetchingStrategy fetchingStrategy) {
switch(fetchingStrategy) {
case BOOK_WITH_NAMES_AND_DESCRIPTIONS:
List<Book> bookList = session.createQuery("from Book").list();
// Notice empty statement in order to start each subselect
for (Book book : bookList) {
for (Name address: book.getNameList());
for (Description description: book.getDescriptionList());
}
return bookList;
}
}
public static enum BookFetchingStrategy {
BOOK_WITH_NAMES_AND_DESCRIPTIONS;
}
}
I have done the following one to populate the database
SessionFactory sessionFactory = configuration.buildSessionFactory();
Session session = sessionFactory.openSession();
session.beginTransaction();
// Ten books
for (int i = 0; i < 10; i++) {
Book book = new Book();
book.setName(RandomStringUtils.random(13, true, false));
// For each book, Ten names and descriptions
for (int j = 0; j < 10; j++) {
Name name = new Name();
name.setSomething(RandomStringUtils.random(13, true, false));
Description description = new Description();
description.setSomething(RandomStringUtils.random(13, true, false));
book.getNameList().add(name);
book.getDescriptionList().add(description);
}
session.save(book);
}
session.getTransaction().commit();
session.close();
And to retrieve
session = sessionFactory.openSession();
session.beginTransaction();
List<Book> bookList = session.createQuery("from Book").list();
for (Book book : bookList) {
for (Name address: book.getNameList());
for (Description description: book.getDescriptionList());
}
session.getTransaction().commit();
session.close();
I see
Hibernate:
select
book0_.id as id0_,
book0_.name as name0_
from
BOOK book0_
Hibernate: returns 100 rows (as expected)
select
namelist0_.BOOK_ID as BOOK3_1_,
namelist0_.id as id1_,
namelist0_.id as id1_0_,
namelist0_.something as something1_0_
from
NAME namelist0_
where
namelist0_.BOOK_ID in (
select
book0_.id
from
BOOK book0_
)
Hibernate: returns 100 rows (as expected)
select
descriptio0_.BOOK_ID as BOOK3_1_,
descriptio0_.id as id1_,
descriptio0_.id as id2_0_,
descriptio0_.something as something2_0_
from
DESCRIPTION descriptio0_
where
descriptio0_.BOOK_ID in (
select
book0_.id
from
BOOK book0_
)
Three select statements. No "n + 1" select problem. Be aware i am using property access strategy instead of field. Keep this in mind.
You can set a batch-size
on your bags, when one unitialized collection is initialized, Hibernate will initialize a some other collections with a single query
More in the Hibernate doc
精彩评论