Asynchronous Programming Model & ADO.NET

J

JumpingMattFlash

I'm trying to execute two queries on a database asynchronously by using the
..NET 2.0 APM. I've created a test harness which executes two queries which
take at least ten seconds each. Using the APM I would've expected the page to
load in a little over ten seconds, but instead it takes over 30.

Here's my code for a single ASP.NET page with two gridviews on. Any pointers
as to where i may be going wrong would be appreciated:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
Handles Me.Load
Dim aResult As IAsyncResult = BeginReader()
Dim aResultTwo As IAsyncResult = BeginSecondReader()
Dim aTable As DataTable = EndReader(aResult)
GridView1.DataSource = aTable
GridView1.DataBind()
Dim aTableTwo As DataTable = EndReader(aResultTwo)
GridView2.DataSource = aTableTwo
GridView2.DataBind()
End Sub

Public Shared Function BeginReader() As IAsyncResult
Dim myConn As New SqlConnection("Data Source=(local);Initial
Catalog=MyDB;Persist Security Info=True;User
ID=test_user;Password=test_user;Async=True;")
Dim myCmd As New SqlCommand("WAITFOR DELAY '00:00:10'; SELECT TOP 10
* FROM tblUser", myConn)
myCmd.CommandType = CommandType.Text
myConn.Open()

Dim asResult As IAsyncResult = myCmd.BeginExecuteReader(Nothing,
myCmd)
Return asResult
End Function

Public Shared Function BeginSecondReader() As IAsyncResult
Dim myConn As New SqlConnection("Data Source=(local);Initial
Catalog=MyDB;Persist Security Info=True;User
ID=test_user;Password=test_user;Async=True;")
Dim myCmd As New SqlCommand("WAITFOR DELAY '00:00:10'; SELECT TOP 10
* FROM tblUser ORDER BY NEWID()", myConn)
myCmd.CommandType = CommandType.Text
myConn.Open()

Dim asResult As IAsyncResult = myCmd.BeginExecuteReader(Nothing,
myCmd)
Return asResult
End Function

Public Shared Function EndReader(ByRef asResult As IAsyncResult) As
DataTable
Dim RetTable As New DataTable
Dim myCmd As SqlCommand = CType(asResult.AsyncState, SqlCommand)
myCmd.EndExecuteReader(asResult)
myCmd.Connection.Close()
Dim myAdapter As SqlDataAdapter = New SqlDataAdapter(myCmd)
myAdapter.Fill(RetTable)
Return RetTable
End Function
 
C

Cor Ligthert[MVP]

I'm trying to execute two queries on a database asynchronously by using
the
.NET 2.0 APM. I've created a test harness which executes two queries which
take at least ten seconds each. Using the APM I would've expected the page
to
load in a little over ten seconds, but instead it takes over 30.
Click to expand...
Why did you think that? Or do you use load balanced servers, give us then
that information, if not then still why?\

Asynchrously processing need only more total processing time but will as
there are more destinations save througput time, however as it has to go
thru one pipe it is still one after the other even as that are packages.

Cor
 
M

Miha Markic

I am not sure how async processing works in ado.net, however I think that in
your case both queries are executed in sequence because they both belong to
same connection. Plus you get a first connection performance hit.
Try using two connections and fire each query on its own connection.
 
W

William Vaughn

Right. MARS... did you enable Multiple Active Resultsets?

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________
Miha Markic said:
I am not sure how async processing works in ado.net, however I think that
in your case both queries are executed in sequence because they both belong
to same connection. Plus you get a first connection performance hit.
Try using two connections and fire each query on its own connection.

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

JumpingMattFlash said:
I'm trying to execute two queries on a database asynchronously by using
the
.NET 2.0 APM. I've created a test harness which executes two queries
which
take at least ten seconds each. Using the APM I would've expected the
page to
load in a little over ten seconds, but instead it takes over 30.

Here's my code for a single ASP.NET page with two gridviews on. Any
pointers
as to where i may be going wrong would be appreciated:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs)
Handles Me.Load
Dim aResult As IAsyncResult = BeginReader()
Dim aResultTwo As IAsyncResult = BeginSecondReader()
Dim aTable As DataTable = EndReader(aResult)
GridView1.DataSource = aTable
GridView1.DataBind()
Dim aTableTwo As DataTable = EndReader(aResultTwo)
GridView2.DataSource = aTableTwo
GridView2.DataBind()
End Sub

Public Shared Function BeginReader() As IAsyncResult
Dim myConn As New SqlConnection("Data Source=(local);Initial
Catalog=MyDB;Persist Security Info=True;User
ID=test_user;Password=test_user;Async=True;")
Dim myCmd As New SqlCommand("WAITFOR DELAY '00:00:10'; SELECT TOP 10
* FROM tblUser", myConn)
myCmd.CommandType = CommandType.Text
myConn.Open()

Dim asResult As IAsyncResult = myCmd.BeginExecuteReader(Nothing,
myCmd)
Return asResult
End Function

Public Shared Function BeginSecondReader() As IAsyncResult
Dim myConn As New SqlConnection("Data Source=(local);Initial
Catalog=MyDB;Persist Security Info=True;User
ID=test_user;Password=test_user;Async=True;")
Dim myCmd As New SqlCommand("WAITFOR DELAY '00:00:10'; SELECT TOP 10
* FROM tblUser ORDER BY NEWID()", myConn)
myCmd.CommandType = CommandType.Text
myConn.Open()

Dim asResult As IAsyncResult = myCmd.BeginExecuteReader(Nothing,
myCmd)
Return asResult
End Function

Public Shared Function EndReader(ByRef asResult As IAsyncResult) As
DataTable
Dim RetTable As New DataTable
Dim myCmd As SqlCommand = CType(asResult.AsyncState, SqlCommand)
myCmd.EndExecuteReader(asResult)
myCmd.Connection.Close()
Dim myAdapter As SqlDataAdapter = New SqlDataAdapter(myCmd)
myAdapter.Fill(RetTable)
Return RetTable
End Function
--
=============
VB .NET Developer
http://www.rocketscience.uk.com
http://info.i-snapshot.com
Click to expand...
Click to expand...
 
Top