Insert SQL Server 2008 Package

Question

I have a dictionary of about 50,000 words; each word has many synonyms, antonyms, etc. I use Fluent NHibernate and I created an instance of MS SQL Server 10gb for the application and I am trying to populate it as a batch update:

public class Word
{
     public virtual int Id { get; set; }
     public virtual string Text { get; set; }
     public virtual IList<Word> Synonyms { get; set; }
     public virtual IList<Word> Antonyms { get; set; }
}

public class WordMapping : ClassMap<Word>
{
    public WordMapping()
    {
        Id(x => x.Id).UnsavedValue(0);
        Map(x => x.Text);

        HasMany(x => x.Synonyms).Cascade.AllDeleteOrphan();
        HasMany(x => x.Antonyms).Cascade.AllDeleteOrphan();
    }
}
...

List<Word> words = loadWordsFromFile();

using (IStatelessSession session = session.SessionFactory.OpenStatelessSession())
    using (var transaction = session.BeginTransaction())
        {
            foreach (var word in words)
                   session.Insert(word);
            transaction.Commit();
        }

I set the batch size to 1000:

 private static ISessionFactory CreateSessionFactory()
 {
    return Fluently.Configure()
                .Database(MsSqlConfiguration
                         .MsSql2008
                         .ConnectionString(connStr)
                         .AdoNetBatchSize(1000))
                .Mappings(M => M.FluentMappings.AddFromAssemblyOf<WordMapping>())
                .ExposeConfiguration(Cfg => _configuration = Cfg)
                .BuildSessionFactory();
 }

It works for hours without any visible results. Is this the best way to populate my database, or is there a better approach when working with large data sets?

Answer 1

Like lFoust, I also use the SQLBulkCopy utility for a fairly large ELT in some databases.

The following is an example of the SqlBulkCopy code I wrote:

SqlConnection sqlCon = new SqlConnection("ConnectionStringHere");
SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(sqlCon);
DataTable dt = new DataTable();

dt.Rows.Add(// add data to the datatable)

using (sqlBulkCopy)
{

.DestinationTableName = "Putnameoftablehere";
.NotifyAfter = dt.Rows.Count/100; //Notify every 1%
.WriteToServer(dt);
.Close();


}

Answer 2

NHibernate, , ( 20+ ), SqlBulkCopy. ( DataTables ..), .