Batch Update in NHibernate

nhibernate

I know I'm late to the party on this, but thought you may like to know this is now possible using HQL in NHibernate 2.1+

session.CreateQuery(@"update Users set Country = 'Antarctica'")
.ExecuteUpdate();

Starting NHibernate 3.2 batch jobs have improvements which minimizes database roundtrips. More information can be found on HunabKu blog. Here is example from it - these batch updates do only 6 roundtrips:

using (ISession s = OpenSession())
using (s.BeginTransaction())
{
    for (int i = 0; i < 12; i++)
    {
        var user = new User {UserName = "user-" + i};
        var group = new Group {Name = "group-" + i};
        s.Save(user);
        s.Save(group);
        user.AddMembership(group);
    }
    s.Transaction.Commit();
}

You can set the batch size for updates in the nhibernate config file.

<property name="hibernate.adonet.batch_size">16</property>

And you don't need to call Session.Update(User) there - just flush or commit a transaction and NHibernate will handle things for you.

EDIT: I was going to post a link to the relevant section of the nhibernate docs but the site is down - here's an old post from Ayende on the subject:

As to whether the use of NHibernate (or any ORM) here is a good approach, it depends on the context. If you are doing a one-off update of every row in a large table with a single value (like setting all users to the country 'Antarctica' (which is a continent, not a country by the way!), then you should probably use a sql UPDATE statement. If you are going to be updating several records at once with a country as part of your business logic in the general usage of your application, then using an ORM could be a more sensible method. This depends on the number of rows you are updating each time.

Perhaps the most sensible option here if you are not sure is to tweak the batch_size option in NHibernate and see how that works out. If the performance of the system is not acceptable then you might look at implementing a straight sql UPDATE statement in your code.

Starting with NHibernate 5.0 it is possible to make bulk operations using LINQ.

session.Query<Cat>()
.Where(c => c.BodyWeight > 20)
.Update(c => new { BodyWeight = c.BodyWeight / 2 });

NHibernate will generate a single "update" sql query.

See Updating entities

You don't need to update, nor flush:

IList<User> users = session.CreateQuery (...).List<User>;
users.Foreach(u=>u.Country="Antartica")
session.Transaction.Commit();

I think NHibernate writes a batch for all the changes.

The problem is, that your users need to be loaded into memory. If it gets a problem, you can still use native SQL using NHibernate. But until you didn't prove that it is a performance problem, stick with the nice solution.

Batch Update in NHibernate

Related

Recent Posts