How N+1 issue can be resolved by introducing second-level cache in Hibernate?

Question

In performance section of Hibernate documentation stated that:

A completely different approach to problems with N+1 selects is to use the second-level cache.

I don't understand how it might resolve problem. What may be real world example and explanation?

Answer 1

It's simple. Let's say you have the following domain model:

@Entity(name = "Post")
public class Post {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    private String name;

    @OneToMany(cascade = CascadeType.ALL, mappedBy = "post")
    private List<Comment> comments = new ArrayList<>();

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public List<Comment> getComments() {
        return comments;
    }

    public void addComment(Comment comment) {
        comments.add(comment);
        comment.setPost(this);
    }
}

@Entity(name = "Comment")
public class Comment {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @ManyToOne
    private Post post;

    public Comment() {
    }

    public Comment(String review) {
        this.review = review;
    }

    private String review;

    public Long getId() {
        return id;
    }

    public Post getPost() {
        return post;
    }

    public void setPost(Post post) {
        this.post = post;
    }

    public void setReview(String review) {
        this.review = review;
    }
}

If you run the following HQL query:

List<Comment> comments = session.createQuery(
    "select c from Comment c ").list();
for(Comment comment : comments) {
    Post post = comment.getPost();
}

Then for each Comment you'll have to run an additional query for fetching the associated comment Post.

If you enable 2nd level caching:

@Entity(name = "Post")
@Cacheable
@org.hibernate.annotations.Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
public class Post {
    ...
}

Then Hibernate first goes to the 2nd level Cache to load the entity and only hits the database if there's no cache entry found.

A simpler solution is to simply fetch all required data at query-time:

List<Comment> comments = session.createQuery(
    "select c from Comment c fetch c.post ").list();

This way you won't run into N+1 query issues, and you won't need a 2nd level cache either. Like any caching solution, the 2nd level cache is prone to inconsistencies when the database is updated outside of Hibernate API.