hibernate One-to-many association : subselect or eager

Question

In a grails application, i have a relationship that is by default lazy:

class Author{
    String name
    static mapping = {
         books lazy:false
    }
}

I have a criteria API query that fetches the Authors. When i run the query

Author.createCriteria().list{
    eq("name", "John")
}

i have N+1 subselects for each Author.

My second approach is to eager fetch the books just like this:

Author.createCriteria().list{
    eq("name", "John")
    fetchMode("books", FetchMode.JOIN)
}

In this case, the N+1 select problem does not appear, however i have a JOIN query.

Which approach is better in terms of performance and optimization ?

dmahapatro dmahapatro · Accepted Answer · 2013-06-08T21:27:21

I think you meant by default eager instead of lazy in the first line of the question.

POINT TO REMEMBER
When associations are included in criteria query, they are by default eagerly fetched.

In your case you do not need the mapping books lazy:false in Author.
In terms of optimization I would vouch for the below approach:

class Author {
    String name
    static hasMany = [books: Book]
    static mapping = {
         //books lazy:false (DO NOT NEED)
    }
}

class Book {
    String bookName
    //set belongsTo if you need bi-directional one-many
    //books gets cascade deleted when author is deleted
    //static belongsTo = [author: Author]
}

//Bootstrap
def author1 = new Author(name: "Dan Brown")
def book1 = new Book(bookName: "Da Vinci Code")
def book2 = new Book(bookName: "Angels And Demons")
def book3 = new Book(bookName: "Inferno")

[book1, book2, book3].each {author1.addToBooks(it)}
author1.save(flush: true, failOnError: true)

//Criteria
Author.createCriteria().list{
    eq("name", "Dan Brown")
    books{
       //books are eagerly fetched by default using inner join. No need to specify FetchMode.
    }
}

In terms of performance I would not go with the above approach if I have each Author who has wrote 20000 blogs(replace books with blogs). Eagerly fetching 20000 blogs for n authors will be a performance hit. In this case I would go for lazy fetch (N + 1) route and try to filter out blogs as per my requirement. Something like below:

def author = Author.findByName("Dan Brown")
def books = author.books
//Assume you have a field publishedYear in Book
def booksReleasedIn2013 = author.books.findAll{it.publishedYear == 2013} 
def booksReleasedBefore2013 = author.books.findAll{it.publishedYear < 2013} 

assert "Inferno" in booksReleasedIn2013*.bookName
assert "Angels And Demons" in booksReleasedBefore2013*.bookName

If you do not have such(blogs case) case, then I would use the example cited above for both optimization and performance.

hibernate One-to-many association : subselect or eager

1 Answers