CODEWITHSUNDEEP
c#sql-servernhibernate
Felipe Santiago
Felipe Santiago

Reputation: 444

C# Nhibernate Save List

Today I had an List with 40.000 registers that I needed to save in my MSSQL DB. When I tried to save it, I checked my console display and I realized that it was saving item by item, and that consumed a lot of time, even with me trying to do it to insert the entire list with the comand below.

List<Andamento> andamentoList = fillAndamentoList(parameters);
_repository.Save(andamentoList);
_repository.Commit();

Is it possible to configure NHibernate and change it to actually insert the entire list with only one database interaction?

Thank you very much,

Upvotes: 6

Views: 5185

Answers (2)

Najera
Najera

Reputation: 2879

For batching using NHibernate you need to use stateless session:

using (var statelessSession = sessionFactory.OpenStatelessSession())
using (var transaction = statelessSession.BeginTransaction())
{
    foreach (var item in andamentoList)
    {
        statelessSession.Insert(item);
    } 
    transaction.Commit();
}

Combine this with the batch size to gain performance in your config file:

<property name="adonet.batch_size">100</property>

You can read more here.

Upvotes: 7

ImplexOne
ImplexOne

Reputation: 549

First things first. If you do not start explicit transaction NHibernate will start an implicit one for each item you save/flush. Starting a transaction is costly. So first thing you need to do is 

using (var transaction = session.BeginTransaction())
{
   //Loop here
   transaction.Commit();
}

The other thing to look at is how many statements does it take to save one entity. If it is more than one you have to create your own batcher (built in cannot handle table-per-class and other cases well).

Upvotes: 0

Related Questions