Select N+1

Select N + 1 is a data access anti-pattern where the database is accessed in a suboptimal way. Take a look at this code sample, then we'll discuss what is going on. Say you want to show the user all comments from all posts so that they can delete all of the nasty comments. The naive implementation would be something like:

// SELECT * FROM Posts
foreach (Post post in session.CreateQuery("from Post").List())
{
//lazy loading of comments list causes:
// SELECT * FROM Comments where PostId = @p0
foreach (Comment comment in post.Comments)
{
//print comment...
}

}

In this example, we can see that we are loading a list of posts (the first select) and then traversing the object graph. However, we access the collection in a lazy fashion, causing NHibernate to go to the database and bring the results back one row at a time. This is incredibly inefficient, and the NHibernate Profiler will generate a warning whenever it encounters such a case.

The solution for this example is simple: Force an eager load of the collection up front. Using HQL:

var  posts = session
.CreateQuery("from Post p left join fetch p.Comments")
.List();

Using the criteria API:

session.CreateCriteria(typeof(Post))
.SetFetchMode("Comments", FetchMode.Eager)
.List();

Using Linq:

var posts = (from post in session.Query<Post>().FetchMany(x => x.Comments)
             select post).ToList();

In each case, we will get a join and only a single query to the database.

Note: this is the classical appearance of the problem. It can also surface in other scenarios, such as calling the database in a loop, or more complex object graph traversals. In those cases, it it generally much harder to see what is causing the issue.

Having said that, NHibernate Profiler will detect those scenarios just as well, and give you the exact line in the source code that cause this SQL to be generated.

Other options for solving this issue are MultiQuery and MultiCriteria, which are also used to solve the issue of Too Many Queries.