Inserting a collection of entities in bulk using Fluent NHibernate

Question

I'm trying to insert a large collection of objects into a table using fluent NHibernate, using a call to save or update passing each entity from the collection in a foreach loop. I had been setting the batch size to the size of the collection, however I've read on Ayende's blog that setting it to large values is not recommended so I've capped it at 250, however when I view the collection in NHProf, I see a stream of insert statements (the collection is about 20000 items) rather than a set of batched insert calls.

This seems very inefficient and is taking a far longer time than I'd expect for a query that is very basic in essence - inserting a value into 25 columns (yes, that is one place this could be made better, but it's a legacy database that I'm stuck with for now) into a SQL Server 2008 database - so I can only assume I'm doing it wrong.

Is there a recommended way to insert large collections of entities using NHibernate? Is there an efficiency gain to be had by using Save over SaveOrUpdate?

Code of the add method - the SetBatchSize call is where the batch is capped to 250, or set to the collection size if it's less than 250:

public void Add(IEnumerable<TEntity> entities)
{
    var session = GetCurrentSession();
    using (var transaction = session.BeginTransaction())
    {
        entities = entities.ToList();

        session.SetBatchSize(SetBatchSize(entities.Count()));

        foreach (var entity in entities)
            session.SaveOrUpdate(entity);

        transaction.Commit();
    }
}

Apologies for the slightly vague question, I get the feeling I'm just approaching things the wrong way and any pointers would be greatly appreciated!

Solution

You'll want to use a StatelessSession for bulk inserts.

(related: Inserts of stateless session of NHibernate are slow)

Comments

[Ian Cotterill]

Thanks, I've looked at the link and also changed over the insert to use a stateless session, and it's not made any difference really. Part of the problem is that this is a brownfield app, and I don't have the option to generate an identity. Is there a way to force NH to perform an insert without requesting scope identity when it inserts a record?

[hometoast]

Disclaimer: there's a way to get NHibernate to do just about anything you want. But -- in this case, you can't change your model. I'd argue NH isn't the proper tool in this case.

[Ian Cotterill]

Thanks, that's kind of the conclusion I'd come to. I've asked specifically about the scope identity in another question, just in case anyone knows any form of wizardry I can employ.

[Malcolm]

Is there a way to force NH to perform an insert without requesting scope identity when it inserts a record? It looks like ISession.Persist() does that.