SQL Server存储过程传递值列表性能问题优化方案求助

首先澄清一个常见误区：你观测到的单条循环插入并非表值参数本身的特性，是Dapper/EF默认批量实现的问题，表值参数本身支持整批提交处理，性能远高于拼接多值INSERT语句。
下面是三种可行的优化方案，按性能从高到低排序：

方案1：存储过程直接处理表值参数（最推荐，兼顾安全与性能） #

这是SQL Server官方推荐的批量插入方案，全程无循环、无字符串拼接、天然参数化避免SQL注入。

操作步骤 #

1. 首先在数据库中定义用户自定义表类型

CREATE TYPE EntryTableType AS TABLE (
    id INT,
    name NVARCHAR(50) -- 字段类型和长度与实际业务匹配
)

2. 改写存储过程，直接从表值参数读取数据插入

CREATE PROCEDURE BulkInsertEntries
    @Entries EntryTableType READONLY -- 表值参数必须标记为READONLY
AS
BEGIN
    SET NOCOUNT ON;
    -- 单条语句完成整批插入，不会拆分为多条INSERT
    INSERT INTO Entries (id, name)
    SELECT id, name FROM @Entries;
END

3. 应用层直接传递集合作为表值参数调用存储过程即可，以C#为例：

var entryList = new List<Entry> { /* 你的数据集合 */ };
var table = new DataTable();
table.Columns.Add("id", typeof(int));
table.Columns.Add("name", typeof(string));
foreach (var entry in entryList)
{
    table.Rows.Add(entry.Id, entry.Name);
}

var parameters = new DynamicParameters();
parameters.Add("@Entries", table.AsTableValuedParameter("EntryTableType"));
connection.Execute("BulkInsertEntries", parameters, commandType: CommandType.StoredProcedure);

方案2：参数化拼接多值INSERT语句 #

如果不适合使用存储过程，可以自行拼接参数化的多值INSERT语句，注意SQL Server单条语句最大支持2100个参数，需要按批次拆分插入。
示例代码（C#）：

var entries = new List<Entry> { /* 你的数据集合 */ };
const int perBatchCount = 1000; // 每批插入1000行，共2000个参数，不超2100上限
for (int i = 0; i < entries.Count; i += perBatchCount)
{
    var currentBatch = entries.Skip(i).Take(perBatchCount).ToList();
    var param = new DynamicParameters();
    var valueParts = new List<string>();
    for (int j = 0; j < currentBatch.Count; j++)
    {
        var item = currentBatch[j];
        valueParts.Add($"(@id{j}, @name{j})");
        param.Add($"id{j}", item.Id);
        param.Add($"name{j}", item.Name);
    }
    var sql = $"INSERT INTO Entries (id, name) VALUES {string.Join(',', valueParts)}";
    connection.Execute(sql, param);
}

方案3：使用SqlBulkCopy（适合万级以上超大量数据插入） #

如果需要插入的数据量超过1万行，SqlBulkCopy是性能最高的方案，底层走批量拷贝协议，开销远低于普通INSERT语句。
示例代码（C#）：

var entries = new List<Entry> { /* 你的数据集合 */ };
using var bulkCopy = new SqlBulkCopy(connection);
// 配置目标表和字段映射
bulkCopy.DestinationTableName = "Entries";
bulkCopy.ColumnMappings.Add("Id", "id");
bulkCopy.ColumnMappings.Add("Name", "name");
// 用Dapper扩展方法将集合转为IDataReader，无需手动构造DataTable
using var dataReader = ObjectReader.Create(entries);
bulkCopy.WriteToServer(dataReader);

内容的提问来源于stack exchange，提问作者Tal Shloman