Saving 1000+ records to the database at a time

Question

I'm using NHibernate currently. I have a situation where I need to save a bunch of records to the database like this:

var relatedTopics = GetRelatedTopics(topic);
foreach (var relatedTopic in relatedTopics /* could be anywhere from 10 - 1000+ */)
{
    var newRelatedTopic = new RelatedTopic { RelatedTopicUrl = relatedTopic, TopicUrl = topic.Name };
    _repository.Save(newRelatedTopic);
}

When there are a ton of records to save this is obviously very taxing having to hit the database that many times. What's a better approach? Is there some sort of batch update I can do? Am I better off using a DataSet?

Thanks

I would accept David P's answer as the solution to the question. — Stefan Steinegger, Feb 11 '10 at 11:25

score 16 · Accepted Answer · answered Jun 11 '09 at 16:54

setting adonet.batch_size could improve the situation.

For that you have to

set adonet.batch_size in the NH configuration

Example:

    m_sessionFactory = Fluently
         .Configure()
         .Database(MsSqlConfiguration
             .MsSql2005
             .ConnectionString(c => c.FromConnectionStringWithKey("testme"))
             )
         .Mappings(m => m.FluentMappings
             .AddFromAssemblyOf<TestImpl>())
         .ExposeConfiguration(config =>
         {
             config.SetProperty("adonet.batch_size", "1");
             m_configuration = config;
         })
         .BuildSessionFactory();

set the batch size on the session just before the save

using (ISession session = m_nhibernateSessionFactory.GetSession())
using (var tx = session.BeginTransaction())
{    
   session.SetBatchSize(1000);     
   foreach (var server in serverz)
   {
      session.SaveOrUpdate(server);
   }
   tx.Commit();
}

Why are you setting m_configuration? I get an error still on SetBatchSize and I was trying to figure out why. I am using Oracle, maybe that doesn't support batch size?? — M Akin, Jan 16 '15 at 15:14

score 5 · Answer 2 · edited Nov 18 '15 at 12:45

5

I believe this is what you are looking for:

Bulk Data Operations With NHibernate's Stateless Sessions

Essentially instead of opening an ISession, you open an IStatelessSession, and in your hibernate.cfg.xml you can set:

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

edited Nov 18 '15 at 12:45

Owen Pauling

9,864
18
50
58

answered Jun 11 '09 at 19:21

David P

3,594
3
35
52

score 3 · Answer 3 · edited Nov 04 '15 at 13:43

I think you have a number of options depending on your situation.

If you can use NHibernate 2.1 alphas you can try to use the new Executable HQL that's available.

http://nhibernate.info/blog/2009/05/05/nh2-1-executable-hql.html

Tobias's answer will work as well. Just setting the batch size will increase the performance respectably.

If you want to dirty your hands with ADO.Net...

Doing Bulk Inserts in Sql Server is possible through the use of Sql Bulk Copy.

An example of that is here: http://dotnetslackers.com/articles/ado_net/SqlBulkCopy_in_ADO_NET_2_0.aspx

To me it seems like you're creating a new entity based off of another entity in the database. To me that seems like an ideal scenario to use a stored procedure.

score 1 · Answer 4 · answered Jun 11 '09 at 16:57

1

DataSet? No. Bulk Insert? Yes.

If you are inserting that many records and the inserts are pretty simplistic, you should look at doing Bulk Inserts and pulling out the ORM.

answered Jun 11 '09 at 16:57

Ty.

3,638
2
22
31

Can you elaborate on that some more? I've never dealt with Bulk inserts before. Thanks! – Micah Jun 11 '09 at 16:58

score 1 · Answer 5 · answered Jun 11 '09 at 17:11

The fastest way to insert records is to generate a text file and use LOAD FILE syntax. Most databases have remarkably fast implementations to importing data files into the databases. For MySQL, see below:

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

For other databases, refer to appropriate manual. This is useful if you are inserting a million records, or thousands of records frequently. Otherwise, the best you can do is create a big SQL with the 1000s of inserts and execute that on your database connection directly, skipping ORMs and their related validations.

Saving 1000+ records to the database at a time

5 Answers5

Linked