CODEWITHSUNDEEP
.netperformanceoracle-databasenhibernateorm
deamon
deamon

Reputation: 92397

Speed up bulk insert operations with NHibernate

I want to speed up bulk insert operations with NHibernate 3.2 on Oracle 11g. To do this I tried

Session.Save(entity);
Session.Flush();
Session.Clear();

... in my foreach loop but got an exception caused by objects missing in the Session:

failed to lazily initialize a collection of role: MyClass.PropertyX, no session or session was closed

Another attempt was to set the batch size:

<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
  <session-factory>
    <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
    <property name="connection.driver_class">NHibernate.Driver.OracleClientDriver</property>
    <property name="connection.connection_string">xxx</property>
    <property name="dialect">NHibernate.Dialect.Oracle10gDialect</property>
    <property name="adonet.batch_size">50</property>
    <property name="query.substitutions">true=1, false=0</property>
    <property name="proxyfactory.factory_class">NHibernate.Bytecode.DefaultProxyFactoryFactory, NHibernate</property>
  </session-factory>
</hibernate-configuration>

additionally I set Session.SetBatchSize(50) in my code an got the following exception:

No batch size was defined for the session factory, batching is disabled. Set adonet.batch_size = 1 to enable batching.

The only location where this exception is thrown is NonBatchingBatcher, so it looks like my session has the wrong batcher.

What is wrong here? How can I speed up batch inserts with NHibernate (without using statlese sessions)?

Upvotes: 16

Views: 28189

Answers (6)

jdagosta
jdagosta

Reputation: 19

Code below works for me on inserting several composite entities

 public static void SqlBulkInsert(this ISession session, DataTable dataTable, string tableName)
    {
        var conn = (SqlConnection)session.Connection;
        using (var cmd = new SqlCommand())
        {
            session.Transaction.Enlist(cmd);
            using (var copy = new SqlBulkCopy(conn, SqlBulkCopyOptions.FireTriggers, cmd.Transaction))
            {
                copy.BulkCopyTimeout = 10000;
                copy.DestinationTableName = tableName;
                foreach (DataColumn column in dataTable.Columns)
                {
                    copy.ColumnMappings.Add(column.ColumnName, column.ColumnName);
                }

                copy.WriteToServer(dataTable);
                copy.Close();
            }
        }
    }

You will have to create a method to fill a DataTable object from the composite entity object you want to persist.

Upvotes: -1

AlexDev
AlexDev

Reputation: 4717

I know the question was about Oracle but for SQL server I was going to write a routine to take the class mappings and generate a DataTable to be used by SqlBulkInsert, but I found out someone already did this.

https://kaylaniam.wordpress.com/2015/03/13/nhibernate-and-sqlbulkcopy/

This is probably the fastest way to do bulk inserts on SQL Server.

Upvotes: 0

Low Flying Pelican
Low Flying Pelican

Reputation: 6054

Following should work,

var testObjects = CreateTestObjects(500000);

var stopwatch = new Stopwatch();
stopwatch.Start();
using (IStatelessSession session = sessionFactory.OpenStatelessSession())
using (ITransaction transaction = session.BeginTransaction())
{
    foreach (var testObject in testObjects)
        session.Insert(testObject);
    transaction.Commit();
}

stopwatch.Stop();
var time = stopwatch.Elapsed;

Ref : http://nhibernate.info/blog/2008/10/30/bulk-data-operations-with-nhibernate-s-stateless-sessions.html

Upvotes: 42

Dav
Dav

Reputation: 1102

All the above tips are very valid and very useful. Wanted to add one to the collection: disable logging. Having your SQL shown in the console slows you down noticeably, as does profiling using NHProf, auto commenting and pretty-formatting the SQL logged via NLog or log4net. In our case setting:

cfg.AutoCommentSql = false;
cfg.LogFormattedSql = false;

decreased our bulk insert time from ~6 seconds to just over 1 second. So while logging will potentially help you nail down more serious problems, it comes with a performance hit of its own!

Upvotes: 6

nalaiqChughtai
nalaiqChughtai

Reputation: 1297

Second URL having worth to read.

Upvotes: 5

bernhardrusch
bernhardrusch

Reputation: 11890

Why are you clearing the session ?

I think you shouldn't clear the session in the loop. To make sure that the changes are written into the database I'd rather use a transaction.

Pseudocode:

foreach (var i in allElements)
{
    using (var tx = session.BeginTransaction())
    {
        ... do what you have to do with the object
        tx.Commit();
    }
}

To speed things up there are other things which may help - you have to define what you really want to do in the loop.

Upvotes: 1

Related Questions