ADONET async execution - connection broken error

Question

I'm experimenting with async query execution for some time.My goal is executing simple SQL statements and dont wait them to finish. The below code works well for 10, 500 or 1000 or even 5000 queries. but for 50000 queries suddenly error comes up and says

"BeginExecuteReader requires an open and available Connection. The connection's current state is open." and sometimes it says "... state is: broken"

this is aspnet test site and I think 50.000 queries can happen. is it me missing something ? shouldn't it work ?

I use windows7 x64 and I belive its something todo with sql connection polling limits. you'll possibly say that 50.000 is too high but I need to avoid this error to trust the code and I don't know how.

ps: In code I open connection but don't close it for test purposes. if I close connection callback function never fires.

any suggestions ? And there are not too much information about this error on google.

Partial Class test
    Inherits System.Web.UI.Page

    Dim cnTest As SqlConnection

    Protected Sub cmdAsyncTest_Click(sender As Object, e As EventArgs) Handles cmdAsyncTest.Click

        Dim s As String = "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"
        Dim sqlstr As String
        Dim x1 As Integer, x2 As Integer, i As Integer

        sqlstr = "INSERT INTO test1 (name,surname,a2) VALUES ('" & s & "','" & s & "',5)"

        Dim cnstr As String = System.Configuration.ConfigurationManager.ConnectionStrings("ConnectionStringLOG").ConnectionString
        cnTest = New SqlConnection(cnstr)
        cnTest.Open()

        watch = Stopwatch.StartNew()
        For i = 0 To 50000
            myExecute_Async(sqlstr)
        Next

    End Sub

    Function myExecute_Async(ByVal sqlstr As String) As String
            Using cmd As New SqlCommand(sqlstr, cnTest)
                cmd.CommandType = CommandType.Text
                cmd.BeginExecuteReader(New AsyncCallback(AddressOf QueryCallback), cmd)
                Return ""
            End Using
    End Function

    Sub QueryCallback(ByVal async As IAsyncResult)
        ' ToDo: something 
    End Sub

End Class



CREATE TABLE [dbo].[test1](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar](50) NULL,
    [surname] [varchar](50) NULL,
    [a2] [int] NULL,
 CONSTRAINT [PK_test1] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

".NET SqlConnection class, connection pooling and reconnection logic" article is not the answer. my problem is with async execution.

I tried to use that code. I tried not to use sun routines:

Dim cnstr As String = System.Configuration.ConfigurationManager.ConnectionStrings("ConnectionStringLOG").ConnectionString
cnTest = New SqlConnection(cnstr)
cnTest.Open()

watch = Stopwatch.StartNew()

For i = 0 To 50000
    Using cmd As New SqlCommand(sqlstr, cnTest)
        '  Return "" & cmd.ExecuteNonQuery()
        cmd.CommandType = CommandType.Text
        cmd.BeginExecuteReader(New AsyncCallback(AddressOf QueryCallback), cmd)
    End Using
Next

this time I got Exception of type 'System.OutOfMemoryException' was thrown. at the cmd.BeginExecuteReader line.

lets say I have logic that needs to run this 50.000 commands. what should I do to avoid memory problems or pooling limits ?

Answer 1

For those who tries to work with heavy transaction load or interested in topic:

Best choice would be hangfire. I experimented threads also, it works but I figured our that hangfire is much more simple and you don't need to concern about web pool restarts, anything could stops IIS services like errors...

I called a class that inserts 10.000 records to sql server and I called it in for..next with 100 times. sure it took long but it worked like a charm. Also I killed IIS processes and then everything stopped. when I started IIS again everything continued from where it should.

I like this solution much much more.

thanks.

Answer 2

After many trials I found out these: (maybe I don't have enough knowledge)

async SQL query execution is difficult. you cannot catch errors easily, if you destroy connection objects you cannot trigger async callback function.

obviously its not designed to execute 50.000 queries at once. If you think it wont happen in real life, I've no objection to that.

instead I used simple solution:

   HostingEnvironment.QueueBackgroundWorkItem(Function(token) myTestClass.myExecute_Async(sqlstr, token))

50.000 queries in for...next statement and execution time was ZERO seconds. of course they are queued, I looked into sql table and every time I run SELECT COUNT(1) FROM test1 I got increasing recordcount for almost 1 minute. it inserted record count is exactlu 50.001 (I stared for-next from zero) and I felt it works more solid. at least I understand whats going on behind the scenes. IIS memory usage went thru 114.000k and return back to 19.000k normally.

I hope this helps someone else at some point.

thanks my friends.