C#操作本地SQL库更新20000条记录耗时超5分钟，如何优化效率？

• 核心问题：SqlCommand的Parameters集合从未清空，每轮Match循环都会持续累加参数，2万条更新会累积6万个冗余参数，数据库执行前的参数解析成本指数级上升，这是耗时超长的首要原因
• 次要问题：拼接多句UPDATE的方式虽然可行，但参数名重复（都用@morale/@condition/@id），存在参数匹配出错的风险，且AddWithValue的隐式类型转换可能增加额外的解析开销
• 额外问题：未显式使用事务，每次执行ExecuteNonQuery都会自动提交事务，1000次提交也会产生不必要的IO开销

方案1：最小改动修复现有代码（性能提升至少10倍） #

不需要调整整体代码结构，仅修复参数累积和事务的问题即可，2万条数据更新耗时可降至1秒内：

public static bool UpdatePlayers(List<Match> matches)
{
    using (var connection = new SqlConnection(Database.myConnectionString))
    {
        connection.Open();
        // 显式开启事务，所有更新完成后一次性提交
        using (var transaction = connection.BeginTransaction())
        using (var cmd = connection.CreateCommand())
        {
            cmd.Transaction = transaction;
            try
            {
                foreach (Match m in matches)
                {
                    cmd.CommandText = "";
                    // 关键：每次循环前清空上一轮的冗余参数
                    cmd.Parameters.Clear();
                    int paramIndex = 0;
                    foreach (Player p in m.Players)
                    {
                        p.Morale = SomeSpecificCalculationWhichMilisecond();
                        p.Condition = SomeSpecificCalculationWhichMilisecond();
                        // 参数名加索引避免重复匹配错误
                        string moraleParam = $"@morale{paramIndex}";
                        string conditionParam = $"@condition{paramIndex}";
                        string idParam = $"@id{paramIndex}";
                        cmd.CommandText += $"UPDATE [Players] SET [Morale] = {moraleParam}, [Condition] = {conditionParam} WHERE [ID] = {idParam};";
                        // 显式指定参数类型，避免AddWithValue的隐式转换开销
                        cmd.Parameters.Add(moraleParam, SqlDbType.Int).Value = p.Morale; // 字段类型请根据实际表结构调整
                        cmd.Parameters.Add(conditionParam, SqlDbType.Int).Value = p.Condition;
                        cmd.Parameters.Add(idParam, SqlDbType.Int).Value = p.ID;
                        paramIndex++;
                    }
                    cmd.ExecuteNonQuery();
                }
                transaction.Commit();
            }
            catch
            {
                transaction.Rollback();
                throw;
            }
        }
    }
    return true;
}

方案2：表值参数（TVP）方案（更安全性能更高） #

如果接受少量调整，用表值参数一次性传输所有待更新数据，单次执行完成全量更新，性能更稳定且无SQL注入风险：

1. 首先在SQL Server创建自定义表类型

CREATE TYPE PlayerUpdateType AS TABLE
(
    ID INT,
    Morale INT,
    Condition INT
)

2. 调整C#代码如下：

public static bool UpdatePlayers(List<Match> matches)
{
    // 先构造所有待更新的数据集
    var updateData = new DataTable();
    updateData.Columns.Add("ID", typeof(int));
    updateData.Columns.Add("Morale", typeof(int));
    updateData.Columns.Add("Condition", typeof(int));

    foreach (var m in matches)
    {
        foreach (var p in m.Players)
        {
            p.Morale = SomeSpecificCalculationWhichMilisecond();
            p.Condition = SomeSpecificCalculationWhichMilisecond();
            updateData.Rows.Add(p.ID, p.Morale, p.Condition);
        }
    }

    using (var connection = new SqlConnection(Database.myConnectionString))
    {
        connection.Open();
        using (var cmd = connection.CreateCommand())
        {
            cmd.CommandText = @"
                UPDATE p 
                SET p.Morale = t.Morale, p.Condition = t.Condition
                FROM [Players] p
                INNER JOIN @UpdateTable t ON p.ID = t.ID
            ";
            var param = cmd.Parameters.AddWithValue("@UpdateTable", updateData);
            param.SqlDbType = SqlDbType.Structured;
            param.TypeName = "PlayerUpdateType";
            cmd.ExecuteNonQuery();
        }
    }
    return true;
}

方案3：简化方案（使用Dapper） #

如果项目允许引入Dapper，代码可大幅简化，Dapper内置了批量参数优化，无需手动处理参数拼接：

public static bool UpdatePlayers(List<Match> matches)
{
    var updateList = matches.SelectMany(m => m.Players)
        .Select(p => new {
            p.ID,
            Morale = SomeSpecificCalculationWhichMilisecond(),
            Condition = SomeSpecificCalculationWhichMilisecond()
        }).ToList();
    
    using (var connection = new SqlConnection(Database.myConnectionString))
    {
        connection.Execute(@"
            UPDATE [Players] 
            SET [Morale] = @Morale, [Condition] = @Condition 
            WHERE [ID] = @ID
        ", updateList);
    }
    return true;
}

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