VB.NET基于TPL异步执行SQL存储过程串行问题求助

问题说明 #

在SQL Server中有大量需按序列执行的存储过程，已配置在表中，每个序列最多含5个存储过程。期望实现：遍历各序列，并行启动当前序列的所有存储过程，待全部执行完成后再启动下一个序列，以此缩短数据处理耗时。当前尝试用TPL实现，但存储过程仍串行执行，且主线程未等待任务完成。

现有代码的核心问题 #

1. Task.Run调用错误：Task.Run(action:=oSP1.ExecuteSQLStoredProcedure())是直接执行方法并传递返回值，而非传递委托，导致方法在主线程串行执行。
2. 异步方法未等待：ExecuteNonQueryAsync()是异步操作，但未用Await等待完成，代码直接标记任务为"Completed"，实际存储过程仍在后台运行。
3. 共享SqlConnection风险：多个线程复用同一个连接，SqlConnection并非线程安全，会导致并发问题。
4. 未等待序列任务完成：主线程直接弹出"Done"，未等待当前序列的所有存储过程执行完毕。

修正方案 #

1. 重构存储过程执行类为异步模式 #

将执行方法改为异步模式，正确等待SQL操作完成，并为每个实例创建独立的数据库连接。

Imports System.Data.SqlClient
Imports System.Data

Public Class clsSQLStoredProcedure
    Private mSQLStoredProcedure As String
    Private mName As String
    Private mStatus As String = "Pending"
    Private mConnectionString As String ' 改用连接字符串，避免共享连接

    Public Property ConnectionString() As String
        Set(ByVal value As String)
            mConnectionString = value
        End Set
        Get
            Return mConnectionString
        End Get
    End Property

    Public Property SQLStoredProcedure() As String
        Set(ByVal value As String)
            mSQLStoredProcedure = value
        End Set
        Get
            Return mSQLStoredProcedure
        End Get
    End Property

    Public Property Name() As String
        Set(ByVal value As String)
            mName = value
        End Set
        Get
            Return mName
        End Get
    End Property

    Public ReadOnly Property Status() As String
        Get
            Return mStatus
        End Get
    End Property

    Public Async Function ExecuteAsync() As Task
        mStatus = "Running"
        Debug.Print($"{mName} Start @ {Now().ToString}")
        Try
            ' 每个任务使用独立连接，自动释放
            Using conn As New SqlConnection(mConnectionString)
                Await conn.OpenAsync()
                Using cmd As New SqlCommand(mSQLStoredProcedure, conn)
                    cmd.CommandTimeout = 0
                    Await cmd.ExecuteNonQueryAsync()
                End Using
            End Using
            mStatus = "Completed"
            Debug.Print($"{mName} Completed @ {Now().ToString}")
        Catch ex As Exception
            mStatus = "Failed"
            Debug.Print($"{mName} Failed @ {Now().ToString} - {ex.Message}")
        End Try
    End Function
End Class

2. 实现序列并行执行及等待逻辑 #

使用Task.WhenAll等待同一序列的所有异步任务完成，再进入下一个序列。

Imports System.Threading.Tasks

Module modThreading
    ' 模拟从数据库读取的序列数据，实际可替换为从表中查询的逻辑
    Private Function GetProcedureSequences() As List(Of List(Of clsSQLStoredProcedure))
        Dim sequences As New List(Of List(Of clsSQLStoredProcedure))
        
        ' 示例序列1：3个并行存储过程
        Dim sequence1 As New List(Of clsSQLStoredProcedure) From {
            New clsSQLStoredProcedure With {
                .Name = "usp_ETLFactCollectorActivity",
                .SQLStoredProcedure = "EXEC CURRO_DW.conform.usp_ETLFactCollectorActivity -99",
                .ConnectionString = gGDM.Database.Connection.ConnectionString
            },
            New clsSQLStoredProcedure With {
                .Name = "usp_ETLDimPerson",
                .SQLStoredProcedure = "EXEC CURRO_DW.conform.usp_ETLDimPerson -99",
                .ConnectionString = gGDM.Database.Connection.ConnectionString
            },
            New clsSQLStoredProcedure With {
                .Name = "TEST",
                .SQLStoredProcedure = "SELECT 1",
                .ConnectionString = gGDM.Database.Connection.ConnectionString
            }
        }
        sequences.Add(sequence1)

        ' 可添加更多序列...
        Return sequences
    End Function

    Public Async Sub RunSequences()
        Dim sequences = GetProcedureSequences()

        For Each sequence In sequences
            Debug.Print($"Starting sequence @ {Now().ToString}")
            ' 为序列中每个存储过程创建异步任务
            Dim tasks As New List(Of Task)
            For Each sp In sequence
                tasks.Add(sp.ExecuteAsync())
            Next
            ' 等待当前序列所有任务完成，再执行下一个序列
            Await Task.WhenAll(tasks)
            Debug.Print($"Sequence completed @ {Now().ToString}")
        Next

        MsgBox("All sequences completed")
    End Sub
End Module

关键改进点 #

• 异步方法正确等待：用Await等待SQL异步操作完成，确保状态更新准确。
• 独立连接实例：每个存储过程任务使用自己的SqlConnection，避免线程安全问题。
• 序列级等待：Task.WhenAll确保同一序列的所有任务都执行完毕后，才进入下一个序列。
• 正确的Task调用：直接传递异步方法委托，确保任务在后台线程并行执行。

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