大型SQL插入：TVF vs BULK插入

Question

什么是从C#应用程序中插入一个巨大的数组（10M个元素）的最快方法？

直到现在，我使用了批量插入。C#应用程序生成一个大型文本文件，并使用BULK INSERT命令进行加载。出于好奇心，我编写了一个简单的自定义CLR表值函数。

[SqlFunction(Name = "getArray", FillRowMethodName = "FillRow")]
        public static IEnumerable getArray(String name)
        {
        return my_arrays[name]; // returns the array I want to insert into db
        }

        public static void FillRow(Object o, out SqlDouble sdo)
        {
            sdo = new SqlDouble((double)o);
        }

并且这个查询：

INSERT INTO my_table SELECT data FROM dbo.getArray('x');

与批量等效操作相比，执行速度快了近2倍。具体结果如下：

批量插入（BULK）- 330秒（包括写入磁盘和插入） 临时表值函数（TVF） - 185秒

当然，这是由于写入开销造成的，但我不知道BULK插入是否有任何内存等效的方法。

所以我的问题是——相比于专为大型插入而创建的BULK，TVF是否更好，或者我漏掉了什么？是否有任何第三种选择？

- Klark

Answer 1

当我需要最后一滴性能时，我使用SqlBulkCopy, 这样你就可以跳过首先将其全部存入磁盘的开销。

SqlBulkCopy接受一个IDataReader，您必须实现它的一些方法。 我总是创建一个class MyBulkCopySource：IDataReader，点击“实施接口”，然后将其原封不动地提供给BulkCopy以查看调用哪种方法。 实现它，再试一次等。 您只需要实现其中的三个或四个，其余部分将不会被调用。

据我所知，这是从C＃程序快速将数据泵入SqlDB的最快方法。

GJ

- gjvdkamp

Answer 2

• 使用SqlBulkCopy
• 每次使用30,000行等大小的块从多个线程中复制数据。
• 不要将数据直接插入到最终表，而是先插入到一个临时表中。
• 然后使用一个连接设置，该设置不会锁定目标表，在两个表之间进行数据复制。

这种做法可以最大程度地减小对最终表的锁定。

- TomTom

---

你确定要使用多线程吗？你是指每个单板计算机都有自己的连接吗？通常我会认为瓶颈要么是网络，要么是磁盘驱动器。我认为使用多个线程会导致争用。 - gjvdkamp

多线程使得多个SBC同时运行。我使用它来加载更大量的数据（600+万），并且使用3个线程将吞吐量提高了一倍。在核心表格上，争用较少。那里的锁定时间很短，而且三个线程使用单独的临时表实例；这样可以保持数据的流动。 - TomTom