TVP+存储过程批量插入性能不佳，寻求技术优化方案

嘿，刚好我之前在项目里实现过一模一样的需求——用TVP+存储过程做批量插入，还要在并发场景下稳定运行，同时返回插入后的Id。结合你用的EF Code First，给你梳理一套完整的实现流程，亲测好用：

1. 先定义SQL Server的表值参数（TVP） #

首先要在数据库里创建和你的FacilityGroup实体匹配的TVP结构（排除自增Id）：

CREATE TYPE dbo.FacilityGroupTVP AS TABLE(
    Name NVARCHAR(255) NOT NULL,
    InternalNotes NVARCHAR(MAX) NULL
)

这个TVP就是我们批量传参的“容器”，字段要和实体的非主键属性严格对应。

2. 编写支持并发+返回ID的存储过程 #

存储过程要处理批量插入，用OUTPUT子句直接返回插入后的完整数据（包括自增Id），同时通过锁粒度控制优化并发性能：

CREATE PROCEDURE dbo.BulkInsertFacilityGroups
    @FacilityGroups dbo.FacilityGroupTVP READONLY
AS
BEGIN
    SET NOCOUNT ON;
    -- 用ROWLOCK缩小锁范围，避免高并发下的表锁阻塞
    INSERT INTO dbo.FacilityGroups (Name, InternalNotes)
    OUTPUT inserted.Id, inserted.Name, inserted.InternalNotes
    SELECT Name, InternalNotes FROM @FacilityGroups
    WITH (ROWLOCK);
END

划重点：OUTPUT inserted.*是拿到插入后Id的核心，WITH (ROWLOCK)能有效降低并发场景下的锁冲突概率。

3. EF Code First侧的配置与调用 #

3.1 补全你的实体类（方便对照） #

public class FacilityGroup 
{ 
    public int Id { get; set; } 
    [Required] 
    public string Name { get; set; } 
    public string InternalNotes { get; set; } 
    public virtual List<FacilityInstance> Facilities { get; set; } = new List<FacilityInstance>();
}

3.2 创建对应TVP的C#模型类 #

和数据库里的TVP结构完全对齐：

public class FacilityGroupTVP
{
    public string Name { get; set; }
    public string InternalNotes { get; set; }
}

3.3 在DbContext里封装调用方法 #

需要把C#集合转换成SQL能识别的TVP参数，然后调用存储过程并映射返回结果：

public class YourDbContext : DbContext
{
    // ... 你的DbSet和其他配置

    public List<FacilityGroup> BulkInsertFacilityGroups(List<FacilityGroupTVP> batchData)
    {
        // 把C#集合转成DataTable（适配TVP参数）
        var tvpDataTable = ConvertToDataTable(batchData);
        
        // 定义TVP参数
        var tvpParam = new SqlParameter("@FacilityGroups", SqlDbType.Structured)
        {
            TypeName = "dbo.FacilityGroupTVP",
            Value = tvpDataTable
        };

        // 调用存储过程并返回插入后的实体（包含Id）
        return Database.SqlQuery<FacilityGroup>(
            "EXEC dbo.BulkInsertFacilityGroups @FacilityGroups",
            tvpParam
        ).ToList();
    }

    // 辅助方法：List转DataTable
    private DataTable ConvertToDataTable<T>(List<T> list)
    {
        var dt = new DataTable();
        foreach (var prop in typeof(T).GetProperties())
        {
            dt.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
        }
        foreach (var item in list)
        {
            var row = dt.NewRow();
            foreach (var prop in typeof(T).GetProperties())
            {
                row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
            }
            dt.Rows.Add(row);
        }
        return dt;
    }
}

3.4 并发场景的额外优化建议 #

• 可以给数据库开启ReadCommittedSnapshot快照隔离，避免读写操作互相阻塞；
• 控制批量插入的批次大小，建议每次插1000-5000条，平衡插入性能和锁压力；
• 如果是极端高并发场景，可以考虑把存储过程的隔离级别设为READ COMMITTED或者SNAPSHOT。

4. 实际调用示例 #

// 准备批量插入的数据
var batchGroups = new List<FacilityGroupTVP>
{
    new FacilityGroupTVP { Name = "办公区域组1", InternalNotes = "临时办公区" },
    new FacilityGroupTVP { Name = "仓储区域组1", InternalNotes = "冷链仓储区" },
    // ... 更多批量数据
};

// 执行插入并获取返回的Id
using (var db = new YourDbContext())
{
    var insertedGroups = db.BulkInsertFacilityGroups(batchGroups);
    foreach (var group in insertedGroups)
    {
        Console.WriteLine($"已插入组ID：{group.Id}，名称：{group.Name}");
    }
}

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