views:

569

answers:

5

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

+6  A: 

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();
    }
    
Tobias Hertkorn
Can you elaborate a little bit please?
Micah
+1  A: 

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.

Ty
Can you elaborate on that some more? I've never dealt with Bulk inserts before. Thanks!
Micah
+1  A: 

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.

Ryan Oberoi
+3  A: 

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://nhforge.org/blogs/nhibernate/archive/2009/05/05/nh2-1-executable-hql.aspx

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.

Min
+2  A: 

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>
David P