寻求ADO.NET批量插入方案——逐行插入速度极慢

针对20K条记录逐行插入耗时1小时的问题，以下是几种高效的ADO.NET批量插入实现方法：

1. 使用SqlBulkCopy（推荐，性能最优） #

SqlBulkCopy是ADO.NET专门为SQL Server设计的批量数据写入组件，它能大幅减少网络往返次数，将数据批量写入数据库，是处理大量插入场景的首选方案。

实现代码 #

private async Task BulkInsertReprocessTsk(List<ReprocessTskChngs> reprocessTskChngs)
{
    using (SqlConnection conn = new SqlConnection(_connectionString))
    {
        await conn.OpenAsync();
        
        // 将数据转换为DataTable适配SqlBulkCopy
        DataTable dt = new DataTable();
        dt.Columns.Add("CRS", typeof(short));
        dt.Columns.Add("TskLOC", typeof(string));
        dt.Columns.Add("PROCESSED", typeof(int));
        
        foreach (var item in reprocessTskChngs)
        {
            dt.Rows.Add(item.CRS, item.TskLOC, item.PROCESSED);
        }
        
        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, null))
        {
            // 映射DataTable列与数据库表列
            bulkCopy.DestinationTableName = "REPROCESS_TSK";
            bulkCopy.ColumnMappings.Add("CRS", "CRS");
            bulkCopy.ColumnMappings.Add("TskLOC", "TskLOC");
            bulkCopy.ColumnMappings.Add("PROCESSED", "PROCESSED");
            
            // 若DATE_ENTERED字段已设置默认值GETDATE()，无需额外处理，数据库会自动填充
            // 若未设置默认值，可在DataTable中添加该列并填充DateTime.Now
            
            // 可设置批次大小控制内存占用，例如：bulkCopy.BatchSize = 1000;
            await bulkCopy.WriteToServerAsync(dt);
        }
    }
}

2. 使用表值参数（Table-Valued Parameters, TVP） #

通过自定义SQL Server表类型，将数据作为表参数传递，既能保持批量插入的高效性，又支持在插入时执行自定义业务逻辑，灵活性更强。

步骤1：创建SQL Server自定义表类型 #

CREATE TYPE dbo.ReprocessTskType AS TABLE
(
    CRS SMALLINT,
    TskLOC VARCHAR(MAX),
    PROCESSED INT
)

步骤2：ADO.NET实现代码 #

private async Task InsertWithTVP(List<ReprocessTskChngs> reprocessTskChngs)
{
    using (SqlConnection conn = new SqlConnection(_connectionString))
    {
        await conn.OpenAsync();
        
        string insertSql = @"
            INSERT INTO REPROCESS_TSK(CRS, TskLOC, PROCESSED, DATE_ENTERED)
            SELECT CRS, TskLOC, PROCESSED, GETDATE()
            FROM @ReprocessTskTable";
        
        using (SqlCommand cmd = new SqlCommand(insertSql, conn))
        {
            // 转换数据为DataTable
            DataTable dt = new DataTable();
            dt.Columns.Add("CRS", typeof(short));
            dt.Columns.Add("TskLOC", typeof(string));
            dt.Columns.Add("PROCESSED", typeof(int));
            
            foreach (var item in reprocessTskChngs)
            {
                dt.Rows.Add(item.CRS, item.TskLOC, item.PROCESSED);
            }
            
            // 添加表值参数
            SqlParameter tvpParam = cmd.Parameters.AddWithValue("@ReprocessTskTable", dt);
            tvpParam.SqlDbType = SqlDbType.Structured;
            tvpParam.TypeName = "dbo.ReprocessTskType";
            
            await cmd.ExecuteNonQueryAsync();
        }
    }
}

3. 批量提交参数化命令（兼容受限场景） #

若无法使用前两种方案，可优化原有逐行插入逻辑，减少SqlCommand创建次数，按批次批量提交参数化语句。

优化代码 #

private async Task BatchInsertReprocessTsk(List<ReprocessTskChngs> reprocessTskChngs, int batchSize = 700)
{
    using (SqlConnection conn = new SqlConnection(_connectionString))
    {
        await conn.OpenAsync();
        
        string baseInsertSql = @"
            INSERT INTO REPROCESS_TSK(CRS, TskLOC, PROCESSED, DATE_ENTERED)
            VALUES ";
        
        // 按批次拆分数据，避免参数数量超过SQL Server默认限制（2100个）
        for (int i = 0; i < reprocessTskChngs.Count; i += batchSize)
        {
            var batch = reprocessTskChngs.Skip(i).Take(batchSize).ToList();
            using (SqlTransaction trans = conn.BeginTransaction())
            {
                try
                {
                    using (SqlCommand cmd = new SqlCommand("", conn, trans))
                    {
                        List<string> valueClauses = new List<string>();
                        for (int j = 0; j < batch.Count; j++)
                        {
                            string paramPrefix = $"@batch{j}_";
                            valueClauses.Add($"({paramPrefix}CRS, {paramPrefix}TskLOC, {paramPrefix}PROCESSED, GETDATE())");
                            
                            cmd.Parameters.Add($"{paramPrefix}CRS", SqlDbType.SmallInt).Value = batch[j].CRS;
                            cmd.Parameters.Add($"{paramPrefix}TskLOC", SqlDbType.VarChar).Value = batch[j].TskLOC;
                            cmd.Parameters.Add($"{paramPrefix}PROCESSED", SqlDbType.Int).Value = batch[j].PROCESSED;
                        }
                        
                        cmd.CommandText = baseInsertSql + string.Join(", ", valueClauses);
                        await cmd.ExecuteNonQueryAsync();
                    }
                    
                    await trans.CommitAsync();
                }
                catch
                {
                    trans.Rollback();
                    throw;
                }
            }
        }
    }
}

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