Consolidating NHibernate open sessions to the DB (coupling NHibernate and DB sessions)

Question

Hi.

We use NHibernate for ORM, and at the initialization phase of our program we need to load many instances of some class T from the DB.

In our application, the following code, which extracts all these instances, takes forever:

public IList<T> GetAllWithoutTransaction()
{
    using (ISession session = GetSession())
    {
        IList<T> entities = session
            .CreateCriteria(typeof(T))
            .List<T>();
        return entities;
        }
    }
}

Using the NHibernate log I found that the actual SQL queries the framework uses are:

{
    Load a bunch of rows from a few tables in the DB (one SELECT statement).

    for each instance of class T
    {
        Load all the data for this instance of class T from the abovementioned rows 
        (3 SELECT statements).
    }
}

The 3 select statements are coupled, i.e. the second is dependent on the first, and the third on the first two. As you can see, the number of SELECT statements is in the millions, giving us a huge overhead which results directly from all those calls to the DB (each of which entails "open DB session", "close DB session", ...), even though we are using a single NHibernate session.

My question is this: I would somehow like to consolidate all those SELECT statements into one big SELECT statement. We are not running multithreaded, and are not altering the DB in any way at the init phase.

One way of doing this would be to define my own object and map it using NHibernate, which will be loaded quickly and load everything in one query, but it would require that we implement the join operations used in those statements ourselves, and worse - breaks the ORM abstraction. Is there any way of doing this by some configuration?

Thanks guys!

Answer 1

This is known as the SELECT N+1 problem. You need to decide where you're going to place your joins (FetchMode.Eager)

Answer 2

If you can write the query as a single query in SQL, you can get NHibernate to execute it as a single query (usually without breaking the abstraction).

It appears likely that you have some relationships / classes setup to lazy load when what you really want in this scenario is eager loading.

There is plenty of good information about this in the NHibernate docs. You may want to start here:

http://www.nhforge.org/doc/nh/en/index.html#performance-fetching