Solving the Select N+1 Problem

time to read 2 min | 329 words

Note, I am currently writing the NH Prof documentation, so you are probably going to get quite a bit of NHibernate tidbits over the next few days.

I want to show the user all the comments from all the posts, so they can delete all the nasty comments. The naןve implementation would be something like:

foreach (Post post in session.CreateQuery("from Post").List()) // SELECT * FROM Posts

foreach (Comment comment in post.Comments) //lazy loading of comments list causes: SELECT * FROM Comments where PostId = @p0
//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 lazily loaded collection, causing NHibernate to go to the database and bring the results 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, we 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:

.SetFetchMode("Comments", FetchMode.Eager)

In both cases, we will get a join and only a single query to the database.
Note, this is the classic 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.
NHibernate Profiler will detect those scenarios as well, and give you the exact line in the source code that cause this SQL to be generated.
Another option for solving this issue is: MutliQuery and MultiCriteria, which are also used to solved the issue of Too Many Queries.