NHibernate Insert

Question

I do some tests with nhibernate and I modify batch_size to get voluminous inserts.

I am using mssql2005 and using db Northwind. I created 1000 objects and inserted them into the database. I changed the batch_size values ​​from 5 to 100, but did not find any performance changes. I get a value of about 300 ms. Using the sql profiler, I see that 1000 sql-paste statements are server-side. Please, help.

app.config

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

the code

  public bool MyTestAddition(IList<Supplier> SupplierList) { var SupplierList_ = SupplierList; var stopwatch = new Stopwatch(); stopwatch.Start(); using (ISession session = dataManager.OpenSession()) { int counter = 0; using (ITransaction transaction = session.BeginTransaction()) { foreach (var supplier in SupplierList_) { session.Save(supplier); } transaction.Commit(); } } stopwatch.Stop(); Console.WriteLine(string.Format("{0} milliseconds. {1} items added", stopwatch.ElapsedMilliseconds, SupplierList_.Count)); return true; } 

Answer 1

The following is a great article on batch processing in Hibernate, which NHibernate builds on and follows:

http://relation.to/Bloggers/BatchProcessingInHibernate

As you can see, the suggested actions are to set a reasonable batch size in the configuration you made, but also call session.flush() and session.clear() every 20 or so entries.

We ourselves used this method and now we can create and save 1000+ objects in seconds.

Answer 2

You can load the target type into a list, and then call System.Data.SqlClient.BulkCopy to bind the data to the target table.

This will handle large volumes.

Answer 3

According to this nhusers post , you see that 1000 attachments on the SQL server should not really be because optimization is performed at a different level. If you really have no performance gain, trying the latest version of NHibernate can help point to permission.

Answer 4

I tried similar things with nh, never got great performance. I remember that I decided to take pictures every 10 records and record every 50 records to improve performance, as with each insertion the process became slower. It really depends on the size of the object, so you can play arnometry with these numbers, maybe you can squeeze some performance out of it.

Answer 5

Calling ITransaction.Commit will clear your session, effectively writing your changes to the database. You call Commit after each save, so there will be an INSERT for each Provider.

I’ll try to call Commit after every 10 suppliers or so, or maybe even at the end of your 1000 suppliers!