SQL Server批量更新存储过程执行缓慢求助

1. 给核心字段添加索引 #

Lock_Details表的FileName是关联查询的核心字段，没建索引的话每次更新/插入都会触发全表扫描，这是15分钟耗时的主要原因。先给该字段创建索引：

-- 如果FileName是唯一标识，建唯一索引效率更高
CREATE UNIQUE NONCLUSTERED INDEX IX_Lock_Details_FileName ON Lock_Details(FileName);

另外，表值参数tblType_Lock_Details默认没有索引，关联时匹配效率极低。可以在存储过程里把数据导入临时表并创建索引：

CREATE PROCEDURE BulkUpdate_Lock_Details
    @BulkUpdate_Lock_Details tblType_Lock_Details READONLY
AS
BEGIN
    SET NOCOUNT ON; -- 减少不必要的消息输出，提升性能

    -- 转存到临时表并创建索引
    SELECT * INTO #Temp_Lock_Details FROM @BulkUpdate_Lock_Details;
    CREATE UNIQUE CLUSTERED INDEX IX_Temp_Lock_Details_FileName ON #Temp_Lock_Details(FileName);

    -- 执行更新
    UPDATE p 
    SET LockStatus = t.LockStatus, 
        UserName = t.UserName, 
        LockTimeStamp = t.LockTimeStamp 
    FROM Lock_Details p
    INNER JOIN #Temp_Lock_Details t ON p.FileName = t.FileName;

    -- 执行插入
    INSERT INTO Lock_Details (FileName, LockStatus, UserName, LockTimeStamp)
        SELECT t.FileName, t.LockStatus, t.UserName, t.LockTimeStamp
        FROM #Temp_Lock_Details t
        WHERE NOT EXISTS (SELECT 1 FROM Lock_Details p 
                          WHERE p.FileName = t.FileName);

    DROP TABLE #Temp_Lock_Details;
END

2. 修正C#代码的参数配置 #

你当前使用AddWithValue没有明确指定表值参数类型，可能导致SQL Server无法生成最优执行计划。改成明确指定参数类型：

using (var sqlCmd = new SqlCommand("BulkUpdate_Lock_Details", sqlConnection))
{
    sqlCmd.CommandType = CommandType.StoredProcedure;

    // 明确指定结构化参数类型和对应的表值类型名称
    var tvpParam = new SqlParameter("@BulkUpdate_Lock_Details", SqlDbType.Structured)
    {
        TypeName = "dbo.tblType_Lock_Details", // 需与你的表值类型全名一致
        Value = dataTable
    };
    sqlCmd.Parameters.Add(tvpParam);

    sqlConnection.Open();
    _ = sqlCmd.ExecuteNonQuery();
    sqlConnection.Close();
}

3. 用SqlBulkCopy替代表值参数（更高效的批量方案） #

如果表值参数性能仍不达标，直接用SqlBulkCopy把DataTable导入临时表后再执行更新插入，这是批量操作的高效方案：

using (var bulkCopy = new SqlBulkCopy(sqlConnection))
{
    bulkCopy.DestinationTableName = "#Temp_Lock_Details";
    sqlConnection.Open();

    // 先创建临时表（字段需与你的DataTable完全匹配）
    using (var createTableCmd = new SqlCommand(@"
        CREATE TABLE #Temp_Lock_Details (
            FileName NVARCHAR(255),
            LockStatus INT,
            UserName NVARCHAR(100),
            LockTimeStamp DATETIME
        )", sqlConnection))
    {
        createTableCmd.ExecuteNonQuery();
    }

    // 批量导入数据
    bulkCopy.WriteToServer(dataTable);

    // 给临时表创建索引
    using (var createIndexCmd = new SqlCommand(
        "CREATE UNIQUE CLUSTERED INDEX IX_Temp_Lock_Details_FileName ON #Temp_Lock_Details(FileName)",
        sqlConnection))
    {
        createIndexCmd.ExecuteNonQuery();
    }

    // 执行更新和插入
    using (var cmd = new SqlCommand(@"
        UPDATE p 
        SET LockStatus = t.LockStatus, 
            UserName = t.UserName, 
            LockTimeStamp = t.LockTimeStamp 
        FROM Lock_Details p
        INNER JOIN #Temp_Lock_Details t ON p.FileName = t.FileName;

        INSERT INTO Lock_Details (FileName, LockStatus, UserName, LockTimeStamp)
            SELECT t.FileName, t.LockStatus, t.UserName, t.LockTimeStamp
            FROM #Temp_Lock_Details t
            WHERE NOT EXISTS (SELECT 1 FROM Lock_Details p 
                              WHERE p.FileName = t.FileName);", sqlConnection))
    {
        _ = cmd.ExecuteNonQuery();
    }

    sqlConnection.Close();
}

4. 检查事务与隔离级别 #

如果存在并发操作，过高的隔离级别（比如Serializable）会导致锁等待时间过长。可以在存储过程开头设置更合理的隔离级别：

SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 默认级别，显式设置更稳妥

或者把更新和插入放在一个显式事务里，减少锁的持有时间：

BEGIN TRANSACTION;

-- 插入更新语句

COMMIT TRANSACTION;

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