VB.NET Run Multiple SQL Commands Asynchronously

Question

I have a whole lot of stored procedures in a SQL Server database that need to be executed in sequence.

I have them setup in a table that have multiple sequences each containing a max of 5 stored procedures - so basically 5x threads:

I would like loop through each sequence and kick off each sequence's stored procedures at the same time. Then when all the stored procedures have completed, the next sequence of stored procedures can be kicked off.

I will be using SQL commands in .NET to kick of each stored procedure, but I need a way for them to all start at the same time and have the process wait for all to finish before moving on to the next sequence.

The idea of this is so that I can save time on processing data. If each stored procedure runs 2 minutes, running 5 at a time will take 2 minutes instead of 10 minutes.

Now I was told TPL would be a good approach. This is my first time working with multi-threading / TPL.

Below is a class that contain the properties and a function to execute a SQL Server stored procedure:

Public Class clsSQLStoredProcedure

Private mConnection As SqlClient.SqlConnection
Private mCommand As SqlClient.SqlCommand
Private mSQLStoredProcedure As String
Private mName As String
Private mStatus As String = "Pending"
Private mFeedback As DataTable
Public Property Connection() As SqlClient.SqlConnection
    Set(ByVal o As SqlClient.SqlConnection)
        mConnection = o
    End Set
    Get
        Return mConnection
    End Get
End Property

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

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

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

Public ReadOnly Property Feedback() As DataTable
    Get
        Return mFeedback
    End Get
End Property

Public Function ExecuteSQLStoredProcedure()

    On Error GoTo Errorhandler
    Debug.Print(mName & " Start @ " & Now().ToString)
    mStatus = "Running"
    If mConnection.State <> ConnectionState.Open Then mConnection.Open()
    mCommand = New SqlClient.SqlCommand(mSQLStoredProcedure, mConnection)
    mCommand.CommandTimeout = 0
    mCommand.ExecuteNonQueryAsync()
    mStatus = "Completed"
    Debug.Print(mName & " Completed @ " & Now().ToString)
    Exit Function

Errorhandler:
    mStatus = "Failed"
    Debug.Print(mName & " Failed @ " & Now().ToString & " - " & Err.Description)

End Function

End Class

This is the code I use to perform tasks (TPL) to start 3 stored procedures at the same time:

 Imports System.Threading
 Imports System.Threading.Tasks

 Module modThreading

Public Sub TestThreading()

    Dim oSP1 As New clsSQLStoredProcedure
    oSP1.SQLStoredProcedure = "EXEC CURRO_DW.conform.usp_ETLFactCollectorActivity -99"
    oSP1.Connection = gGDM.Database.Connection
    oSP1.Name = "usp_ETLFactCollectorActivity"
    'oSP1.ExecuteSQLStoredProcedure()
    Task.Run(action:=oSP1.ExecuteSQLStoredProcedure())

    Dim oSP2 As New clsSQLStoredProcedure
    oSP2.SQLStoredProcedure = "EXEC CURRO_DW.conform.usp_ETLDimPerson -99"
    oSP2.Connection = gGDM.Database.Connection
    oSP2.Name = "usp_ETLDimPerson"
    'oSP2.ExecuteSQLStoredProcedure()
    Task.Run(action:=oSP2.ExecuteSQLStoredProcedure())

    Dim oSP3 As New clsSQLStoredProcedure
    oSP3.SQLStoredProcedure = "SELECT 1"
    oSP3.Connection = gGDM.Database.Connection
    oSP3.Name = "TEST"
    'oSP3.ExecuteSQLStoredProcedure()
    Task.Run(action:=oSP3.ExecuteSQLStoredProcedure())


    MsgBox("Done")

End Sub

End Module

However they still seem to run one after the other even though some are suppose to run instant while other take about 1min.

When your question from 3 days ago was closed it had been suggested that "you should start by looking into the Tasks Parallel Library (TPL)," not necessarily that it would be a good approach. It seems you need to keep reading up on the TPL documentation so that you could incorporate methods like Task.WaitAll into your solution. — AlwaysLearning
– AlwaysLearning, Commented Dec 15, 2022 at 8:16
Hence why I am posting here, maybe someone can have some insight in a better approach to get this solution working. — LordRofticus
– LordRofticus, Commented Dec 15, 2022 at 10:08
I have also tried the WaitAll but the StoredProcedure still dont run asynchronously — LordRofticus
– LordRofticus, Commented Dec 15, 2022 at 10:14

djv · Accepted Answer · 2022-12-15 15:19:44Z

Don't keep your connection open for the life of your application. You should open and close a database connection as late and early as possible, respectively. A database connection runs on a single thread so the first task, although being run asynchronously, will block the second, et cetera.

So create a new connection for each stored procedure and perhaps WaitAll at the end. You can put the Tasks in a collection to do this.

Dim gGDM1 As New DatabaseContext()
Dim gGDM2 As New DatabaseContext()
Dim gGDM3 As New DatabaseContext()

Try
    Dim oSP1 As New clsSQLStoredProcedure
    oSP1.SQLStoredProcedure = "EXEC CURRO_DW.conform.usp_ETLFactCollectorActivity -99"
    oSP1.Connection = gGDM1.Database.Connection
    oSP1.Name = "usp_ETLFactCollectorActivity"

    Dim oSP2 As New clsSQLStoredProcedure
    oSP2.SQLStoredProcedure = "EXEC CURRO_DW.conform.usp_ETLDimPerson -99"
    oSP2.Connection = gGDM2.Database.Connection
    oSP2.Name = "usp_ETLDimPerson"

    Dim oSP3 As New clsSQLStoredProcedure
    oSP3.SQLStoredProcedure = "SELECT 1"
    oSP3.Connection = gGDM3.Database.Connection
    oSP3.Name = "TEST"

    Dim tasks As New List(Of Task)()
    tasks.Add(Task.Run(oSP1.ExecuteSQLStoredProcedure))
    tasks.Add(Task.Run(oSP2.ExecuteSQLStoredProcedure))
    tasks.Add(Task.Run(oSP3.ExecuteSQLStoredProcedure))
    Task.WaitAll(tasks.ToArray())
Finally
    gGDM1.Dispose()
    gGDM2.Dispose()
    gGDM3.Dispose()
    MsgBox("Done")
End Try

You need to modify the Dim gGDM As New DatabaseContext() lines to create a new context, however you have done it globally, but instead for each call. They are immediately disposed of when you're done.

Collectives™ on Stack Overflow

VB.NET Run Multiple SQL Commands Asynchronously

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related