Processing large number of records with NHibernate

what is the best approach to load a collection and update all items with NHibernate. The current code loads 50 objects and processes each in its own transaction (if 1 fail others are OK).

NH Profiler says that there are too many sql queries per session.

After all, what do you think about this code?

using (var session = sessionFactory.OpenSession())
        {
            var myCollection =
                (from obj in session.Query<MyObject>()
                 select obj).Take(50);

            foreach开发者_运维问答 (var item in myCollection)
            {
                using (var tx = session.BeginTransaction())
                {
                    try
                    {
                        //  Do some stuff...
                        session.Update(item);
                        tx.Commit();
                    }
                    catch (Exception)
                    {
                        tx.Rollback();
                    }
                }
            }
        }

From NHibernate: Streaming large result sets :

NHibernate is meant to be used in an OLTP system, as such, it is usually used in cases where we want to load a relatively small amount of data from the database, work with it and save it back. For reporting scenarios, there are better alternatives, usually (and before you ask, any reporting package will do. Right tool for the job, etc).

But there are cases where you want to do use NHibernate in reporting scenarios nonetheless. Maybe because the reporting requirements aren’t enough to justify going to a separate tool, or because you want to use what you already know. It is in those cases where you tend to run into problems, because you violate the assumptions that were made while building NHibernate.

using (IStatelessSession s = sessionFactory.OpenStatelessSession())
{
    var books = new ActionableList<Book>(book => Console.WriteLine(book.Name));
    s.CreateQuery("from Book")
        .List(books);

}

The stateless session, unlike the normal NHibernate session, doesn’t keep track of loaded objects, so the code here and the data reader code are essentially the same thing.

Basically, use a stateless session and batching. Read also: NHibernate Perf Tricks

How many query do you have ? Maybe you afre suffering the select N+1 problem, because you are acceding lazy properties on each object in your "do stuff" part. Try to load eagerly all the portion you need on the object joining them on the first query.