Batch Update in NHibernate

Question

Does batch update command exist in NHibernate? As far as I am aware it doesn't. So what's the best way to handle this situation? I would like to do the following:

1. Fetch a list of objects ( let's call them a list of users, List<User> ) from the database
2. Change the properties of those objects, ( Users.Foreach(User=>User.Country="Antartica")
3. Update each item back individually ( Users.Foreach(User=>NHibernate.Session.Update(User)).
4. Call Session.Flush to update the database.

Is this a good approach? Will this resulted in a lot of round trip between my code and the database?

What do you think? Or is there a more elegant solution?

Answer 1

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();

Answer 2

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();
}

Answer 3

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

Answer 4

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.

Answer 5

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.

Answer 6

No it's not a good approach!

Native SQL is many times better for this sort of update.

UPDATE USERS SET COUNTRY = 'Antartica';

Just could not be simpler and the database engine will process this one hundred times more efficiently than row at a time Java code.