asp.net - SQL Server 2008批量插入

Question

我有一本大约有50,000个单词的字典；每个单词都有许多同义词、反义词等。我正在使用 Fluent NHibernate，并且我已经为该应用程序创建了一个 10gb MS SQL Server 实例，我正在尝试将其填充为批量更新:

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();
        }

我已将批量大小设置为 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();
 }

它已经运行了几个小时，看不到结束的迹象。这是填充数据库的最佳方法还是在处理大型数据集时是否有更方便的方法？

Answer 1

像 lFoust 一样，我也使用 SQLBulkCopy 实用程序将相当大的 ELT 写入某些数据库。

下面是我编写的 SqlBulkCopy 的代码示例:

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();


}