How can I stop System.OutOfMemoryException on bulk insert.

Question

Hi I am bringing about 100000 records into memory cleaning the data and inserting into a new table. After inserting around 2000 records I get the following exception.

A first chance exception of type 'System.OutOfMemoryException' occurred in Iesi.Collections.DLL A first chance exception of type 'FluentNHibernate.Cfg.FluentConfigurationException' occurred in FluentNHibernate.DLL

I am using Fluent NHibernate. but am not sure if the problem is related to fluent NHibernate itself.

What is the best way to do bulk inserts to the db, I am wondering whether to use stringbuilder and build an sql query instead. Any pointers much appreciated.

Answer 1

Try using a stateless session:

object[] objectsToInsert = GetObjectsToInsert();

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

Answer 2

Whilst I'm sure that there are many techniques that you can employ to do this kind of operation through hibernate, it seems likely that you would be better off constructing your data-cleansing operation as an SQL statement and running it on the database itself. If you need to run it regularly, you might consider a stored procedure.