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

databasenhibernateorm

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!

Best Answer

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

Related Topic