CODEWITHSUNDEEP
nhibernate
harishr
harishr

Reputation: 18065

nhibernate : executing updates in batches

I am trying to do batch updates using NHibernate, but it is not doing batch updates, its doing individual writes for all the rows. I have to write around 10k rows to db.

        using (var session = GetSessionFactory().OpenStatelessSession())
        {
            session.SetBatchSize(100);
            using (var tx = session.BeginTransaction())
            {
                foreach (var pincode in list)
                {
                    session.Update(pincode);
                }
                tx.Commit();
            }
        }

I have tried setting batch size to 100 using session.SetBatchSize(100); but that does not help. Also tried setting batch size using cfg.SetProperty("adonet.batch_size", "100"); but thats also not helping.

I am using GUID primary keys, hence I dont understand the reason for batch update failure. This is exactly the solution explained here. But its not working for me.

NOTE I have version field for optimistic concurrency mapped on all the entities. can that be the culprit for not having batch updates??

EDIT

i tried using state-ful session but that also did not help

        //example 2
        using (var session = GetSessionFactory().OpenSession())
        {
            session.SetBatchSize(100);
            session.FlushMode = FlushMode.Commit;
            foreach (var pincode in list)
            {
                session.Update(pincode);
            }
            session.Flush();
        }

       //example 3
        using (var session = GetSessionFactory().OpenSession())
        {
            session.SetBatchSize(100);
            using (var tx = session.BeginTransaction())
            {
                foreach (var pincode in list)
                {
                    session.Update(pincode);
                }
                tx.Commit();
            }
        }

example 2 for some reason is causing double round trips.

EDIT

after further research I found that, each session.Update is actually updating the db

        using (var session = SessionManager.GetStatelessSession())
        {
            session.SetBatchSize(100);
            foreach (var record in list)
            {
                session.Update(record);
            }
        }

how can I avoid that.

EDIT

tried with flush mode as well, but thats also not helping

        using (var session = SessionManager.GetNewSession())
        {
            session.FlushMode = FlushMode.Never;
            session.SetBatchSize(100);
            session.BeginTransaction();
            foreach (var pincode in list)
            {
                session.SaveOrUpdate(pincode);
            }
            session.Flush();
            session.Transaction.Commit();
        }

EDIT 4

even below one is not working, given i am fetching all entities in same session and updating and saving them in that session only...

        using (var session = SessionManager.GetSessionFactory().OpenSession())
        {
            session.SetBatchSize(100);
            session.FlushMode = FlushMode.Commit;
            session.Transaction.Begin();
            var list = session.QueryOver<Pincode>().Take(1000).List();
            list.ForEach(x => x.Area = "Abcd" + DateTime.Now.ToString("HHmmssfff"));
            foreach (var pincode in list) session.SaveOrUpdate(pincode);
            session.Flush();
            session.Transaction.Commit();
        }

Upvotes: 1

Views: 2605

Answers (3)

harishr
harishr

Reputation: 18065

nhibernate does not batch versioned entities that was the issue in my case.

There is no way you can batch version entities, the only to do this is to make the entity non versioned.

Upvotes: 2

Stefan Steinegger
Stefan Steinegger

Reputation: 64648

Note that:

  • Batches are not visible in Sql Server Profiler. Do not depend on that.
  • When inserting using identity (or native) id generators, NH turns off ado.net batch size.

Additional notes:

  • make sure that you do not have a query for each changed entity, because it flushes before queries.
  • You probably should not call session.Update. In the best case, it doesn't do anything. In worst case, it really does updating thus breaking batching.

When doing having many objects in the session, don't forget to care about flushes and flush time. Sometimes flushing is more time consuming than updating. NH flushes before commit, when you call flush and before queries, unless you turned it off or you use a stateless session. Make sure that you only flush once.

Upvotes: 2

Oskar Berggren
Oskar Berggren

Reputation: 5647

You are using a stateless session. Since a stateless session has no state, it cannot remember anything to do later. Hence the update is executed immediately.

Upvotes: 2

Related Questions