CODEWITHSUNDEEP
c#sql-servernhibernate
Yurii Hohan
Yurii Hohan

Reputation: 4171

Nhibernate large transactions, flushes vs locks

I am having a challenge of maintaining an incredibly large transaction using Nhibernate. So, let us say, I am saving large number of entities. If I do not flush on a transaction N, let us say 10000, then the performance gets killed due to overcrowded Nh Session. If I do flush, I place locks on DB level which in combination with read committed isolation level do affect working application. Also note that in reality I import an entity whose business logic is one of the hearts of the system and on its import around 10 tables are affected. That makes Stateless session a bad idea due to manual maintaining of cascades.

Moving BL to stored procedure is a big challenge due to to reasons:

  1. there is already complicated OO business logic in the domain classes of application,
  2. duplicated BL will be introduced.

Ideally I would want to Flush session to some file and only then preparation of data is completed, I would like to execute its contents. Is it possible?

Any other suggestions/best practices are more than welcome.

Upvotes: 1

Views: 1453

Answers (1)

MichaC
MichaC

Reputation: 13381

You scenario is a typical ORM batch problem. In general we can say that no ORM is meant to be used for stuff like that. If you want to have high batch processing performance (not everlasting locks and maybe deadlocks) you should not use the ORM to insert 1000s of records.

Instead use native batch inserts which will always be a lot faster. (like SqlBulkCopy for MMSQL)

Anyways, if you want to use nhibernate for this, try to make use of the batch size setting. Call save or update to all your objects and only call session.Flush once at the end. This will create all your objects in memory...

Depending on the batch size, nhibernate should try to create insert/update batches with this size, meaning you will have lot less roundtrips to the database and therefore fewer locks or at least it shouldn't take that long...

In general, your operations should only lock the database the moment your first insert statement gets executed on the server if you use normal transactions. It might work differently if you work with TransactionScope.

Here are some additional reads of how to improve batch processing.

http://fabiomaulo.blogspot.de/2011/03/nhibernate-32-batching-improvement.html NHibernate performance insert http://zvolkov.com/clog/2010/07/16?s=Insert+or+Update+records+in+bulk+with+NHibernate+batching

Upvotes: 1

Related Questions