c# - 从 C# SQLCLR 存储过程批量插入 SQL 表的最快方法

Question

我有一个 C# SQLCLR 存储过程，其中首先将数据填充到一些字典中并进行计算，然后将输出存储在其他一些字典中。

由于使用了字典，这个计算完成得非常快，完全证明了我需要使用 CLR 存储过程而不是普通的 SQL 存储过程。

但是，我必须将这些输出字典中的数据保存在 SQL 中的某些表中，这部分需要花费很多时间并且无法满足我对整个 SQLCLR 过程更快的需求。

我必须迭代每个输出字典的每个键，然后必须创建插入查询，然后必须按以下方式运行 ExecuteNonQuery:

那么我该如何改进我的这种方法，以便插入数据时不花时间。我不能将 SqlBulkCopy 用作 it does not accept the in-process Context Connection (即 "Context Connection = true;")作为 connectionString。那么还有其他更快的方法可用吗？提前致谢。

Answer 1

您有几个选择:

1. 创建用户定义的表类型 (UDTT) 和接受该 UDTT 作为参数(即表值参数 (TVP))的 T-SQL 存储过程。由于集合中已有数据，绝对不要创建一个单独的 DataTable将数据传输到 as 那只是浪费时间、内存和 CPU。您可以通过创建返回 IEnumerable<SqlDataRecord> 的方法将其 native 集合中的数据直接流式传输到 TVP。 , 使用 yield return; , 并用作 SqlParameter 的值代表UDTT。我在 SO 上有两个这样的例子:

   • How can I insert 10 million records in the shortest time possible?
   • Pass Dictionary<string,int> to Stored Procedure T-SQL

2. 如果只有 1 个表要填充(因此有 1 个结果集)，那么您可以将集合作为结果集从存储过程中输出并按如下方式使用:

   INSERT INTO schema.table (column_list)
     EXEC schema.SQLCLR_stored_procedure @param1, ....;
   

3. 如果只有 1 个表要填充(因此有 1 个结果集)，并且如果您没有在上下文连接上做任何违反标量函数规则的事情，那么您可以试试把SQLCLR存储过程改成TVF，返回IEnumerable ，并通过 yield return; 遍历返回值流出来。几年前我在 SQL Server Central 上写了一篇文章，其中有一个例子:CLR Table-Valued Function Example with Full Streaming (STVF / TVF)