Fetching multiple bags efficiently

Question

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.

Answer 1

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

Answer 2

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.