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

已经运行了数小时，还没有结束的迹象。在处理大数据集时，这是填充数据库的最佳方式，还是有更快捷的方法？

- Wesley Tansey

---

如果程序运行了几个小时仍然没有完成，那么肯定出了问题。我通常在更短的时间内就可以插入这么多数据。您确定插入是最慢的部分吗？或者是读取文件时的问题？ - Matthew Talbert

使用HasMany关系，可能会有大约一百万行数据。 - Wesley Tansey

它是否尝试在一个插入操作中完成所有内容，如果是的话，它将会在内存中保留每个更改的副本以进行回滚。我们之前使用c#中的Linq2SQL也遇到了类似的情况，在找到如何刷新插入到数据库后解决了这个问题。 - David Mårtensson

ORM（如NHibernate）在性能方面是可怕的。我想对于少数用户来说还好，但如果你正在处理大量用户或大量数据，则确实需要使用SqlCommand和SqlBulkCopy的混合。 - Jonathan Allen

对于宽行，我通常使用10,000的批处理大小。对于只有几列的窄行，我可以看到将批处理大小增加到50,000或更多。即使在适度的硬件上，使用SqlBulkCopy仍然只需要不到一分钟。 - Jonathan Allen

Answer 1

我和lFoust一样，也经常使用SQLBulkCopy实用程序将大量数据加载到某些数据库中。

以下是我编写的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();


}

- Robbie Tapping

---

那段代码似乎不太合理，从using子句开始。 - Wesley Tansey

抱歉Wesley，我已经将Using语句更改为sqlBulkCopy而不是sBulkCopy。如果您需要使用它，我相信您可以解决其他部分。MSDN有很多关于SqlBulkCopy的信息。 - Robbie Tapping

BulkCopy非常快。为了获得最佳性能，请确保您数据表中列的顺序与目标表中的顺序匹配。 - DanB

Answer 2

这可能与您正在使用的NHibernate数据访问层略有不同，但在我们团队将大量数据加载到数据库中时（我们有几个20+ TB的数据库），我们使用SqlBulkCopy。虽然技术上并不是最令人兴奋的（使用DataTable等），但它的工作速度非常快且效果很好。

- Luke Foust