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 ?

That's what I tried first. but at the end of myExecute_Async, when you close connection (or go outside of using) connection disappears from memory and callback function is not called. very strange but also you got no error messages. callback function simply does not hit. — AykutCanturk, Mar 22 '15 at 09:48
sorry, ".NET SqlConnection class, connection pooling and reconnection logic" article is not the answer. my problem is with async execution. thanks anyway. — AykutCanturk, Mar 22 '15 at 09:50
I tried to open connection in subroutine by "using" command. In that case async execution losts all advatages to me. speed is same as synchronized execution. even longer. 5000 sync command takes 26 second, 5000 async command takes 32 seconds. — AykutCanturk, Mar 22 '15 at 11:28
Yeah, don't do that, I misread your question. I suggest you use [TPL](https://msdn.microsoft.com/en-us/library/dd460717%28v=vs.110%29.aspx) instead. And if you're using VS >= 12 then use the [Async and Await](https://msdn.microsoft.com/en-us/library/hh191443.aspx) keywords. — Bjørn-Roger Kringsjå, Mar 22 '15 at 11:39

score 1 · Accepted Answer · answered Mar 25 '15 at 11:15

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.

score 0 · Answer 2 · answered Mar 22 '15 at 12:36

0

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.

answered Mar 22 '15 at 12:36

AykutCanturk

101
11

A better solution would be to not issue all 50k statements at once but process them on N threads where N is subject to experimentation (likely near the number of cores). – usr Mar 22 '15 at 12:43
yeah, QueueBackgroundWorkItem does that. I know 50.000 queries are high but I need to know what framework does under this load. – AykutCanturk Mar 23 '15 at 17:42
All other work in the same process will be drowned out. The rest of the website will halt. This is not a good solution. – usr Mar 23 '15 at 20:22
Yeap my friend you're right. I got the point. As this was a test for heavy transactions for real world, I got my lessons. Threads looks like only logical solution as you said, thanks. – AykutCanturk Mar 24 '15 at 21:28

ADONET async execution - connection broken error

2 Answers2

Linked