Inserts of stateless session of NHibernate are slow

Question

It's been a couple of days that I'm working on improving NHibernate Insert performance.

I'd read in many posts (such as this one) that stateless session can insert like 1000~2000 records per second.... However the best time that it could insert 1243 records is more than 9 seconds for me :

var sessionFactory = new NHibernateConfiguration().CreateSessionFactory();
using (IStatelessSession statelessSession = sessionFactory.OpenStatelessSession())
{
   statelessSession.SetBatchSize(adjustmentValues.Count);

   foreach (var adj in adjustmentValues)
      statelessSession.Insert(adj);
}

The class :

public partial class AdjustmentValue : PersistentObject, IFinancialValue
{
    public virtual double Amount { get; set; }
    public virtual bool HasManualValue { get; set; }
    public virtual bool HasScaleValue { get; set; }
    public virtual string Formula { get; set; }
    public virtual DateTime IssueDate { get; set; }

    public virtual CompanyTopic CompanyTopic { get; set; }
}

Map of the class :

public class AdjustmentValueMap : ClassMap<AdjustmentValue>
{
    public AdjustmentValueMap()
    {
       Id(P => P.Id);

       Map(p => p.Amount);
       Map(p => p.IssueDate);
       Map(p => p.HasManualValue);
       Map(p => p.HasScaleValue);
       Map(p => p.Formula);

       References(p => p.CompanyTopic)
           .Fetch.Join();
    }
}

Am I missing something? Any idea how to speed up the inserts?

The generated queries will be same as below :

Link

Answer 1

from the looks of your NHProf results you are using identity as your POID. Therefore you cannot take advantage of batched writes. every insert/update/delete is a separate command. that is why it's taking so long.

if you change your POID to hilo, guid or guid.comb and set the batch size to 500 or 1000 then you will see a drastic improvement in the write times.

Housemaid

Answer 2

I'm assuming you are using SQL Server 2008.

Few things that come to mind. Are you using the identity key (select SCOPE_IDENTITY() in your sample output) as a primary key for your entities? If yes then I believe NHibernate has to execute the SCOPE_IDENTITY() call for each object before the object is actually saved into the database. So if you are inserting 1000 objects Nhibernate will generate 1000 INSERT statements and 1000 select SCOPE_IDENTITY() statements.

I'm not 100% sure but it might also break the batching. Since you are using NHProf then what does it say? Does it show that all the statements are batched together or can you select individual INSERT statement in the NHProf UI? If your inserts are not batched then you will most likely see "Large number of individual writes" alert in NHProf.

Edit:

If you cannot change your identity generation then you could use SqlBulkCopy. I have used it with NHibernate in data migration and it works. Ayende Rahien has sample on his blog which gets you started.

Majka

For bulk inserts where time matters then I would really go for the SQLBulkCopy solution. – Flare