AJAX, WebMethods and Open vs OpenAsync

Question

User1738843376 posted

Hi,

I've been wondering what is the best way to program JQuery AJAX  calls from a WebForms Website page to webmethods that themselves connect to a SQLServer DB.

Imagine that my homepage apart from other more mundane tasks like accessing the database to build the menu structure, or to get a more fundamental piece of info that must be shown already when the page is still processing all the requests, it still needs to perform 5x AJAX calls via JQuery -> WebMethod. 

Each webmethod makes multiple calls to the DB, some of them quite heavy, before it returns the results to the page.

I've traditionally been using the following code to connect to the DB on each connection required during the webmethods processing:

    Public Overloads Function ReadSQLDataWithCommand(ByVal cmd As SqlCommand) As DataSet
        Dim dataSet As New DataSet
        Dim dataAdapter As New SqlDataAdapter(cmd)
        Dim myConnection As New SqlClient.SqlConnection(dbConnString)

        If myConnection.State = ConnectionState.Open Then
            myConnection.Close()
        End If

        Try
            myConnection.Open()
            With dataAdapter
                .SelectCommand.Connection = myConnection
                .Fill(dataSet)
            End With
        Catch sqlError As SqlTypes.SqlTypeException
            'Log here
        Catch ex As Exception
            'Log here
        Finally
            myConnection.Close()
            dataAdapter.Dispose()
        End Try

        Return dataSet
    End Function

But should i be using

    Public Async Function ReadSQLDataWithCommandAsync(ByVal cmd As SqlCommand) As Task(Of DataSet)
        Dim dataSet As New DataSet
        Dim dataAdapter As New SqlDataAdapter(cmd)
        Dim myConnection As New SqlClient.SqlConnection(dbConnString)

        If myConnection.State = ConnectionState.Open Then
            myConnection.Close()
        End If

        Try
            Await myConnection.OpenAsync()
            With dataAdapter
                .SelectCommand.Connection = myConnection
                .Fill(dataSet)
            End With
        Catch sqlError As SqlTypes.SqlTypeException
            'Log here
        Catch ex As Exception
            'Log here
        Finally
            myConnection.Close()
            dataAdapter.Dispose()
        End Try

        Return dataSet
    End Function

to achieve better performance?

1. Would this speed up page load times, allowing a more 'real async' call by having all the webmethods to really be getting a 'simultaneous' processing?
2. Will the same database connection bottleneck still be present?
3. Is the fact that i'm using a webmethod already producing all the 'asyncronously' possible, and the usage of the OpenAsync will not produce any measurable performance increase?

Your thoughts on this matter would be much appreciated guys,

Thanks

Answer 1

User475983607 posted

Would this speed up page load times, allowing a more 'real async' call by having all the webmethods to really be getting a 'simultaneous' processing?

The async/await pattern does not make code run faster.  If you had more than one DB call then the entire method would take the time it take to execute the longest call.

Will the same database connection bottleneck still be present?

The async/await pattern does not decrease the number of connections.  

Is the fact that i'm using a webmethod already producing all the 'asyncronously' possible, and the usage of the OpenAsync will not produce any measurable performance increase?

Open() blocks the main thread while OpenAsync() does not.  The async/await pattern manages thread resources more efficiently which allows a server to handle a larger volume of requests.

https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/concepts/async/

Answer 2

User1738843376 posted

Hi mgebhard,

Thanks for your input.

mgebhard

The async/await pattern does not make code run faster.  If you had more than one DB call then the entire method would take the time it take to execute the longest call.

You mean that If i have an object being populated, for instance, a category of some sort, and this category requires 1 initial db call to get its main details, and then 3 other, 1 to get the associated media, another to get the associated products, and finally one to list the associated news items, i could benefit by using the openAsync, allowing me to be processing all the requests 'simultaneously' instead of having them wait for the first to finish before starting the second one, and so on? So, if the first call takes 2secs, and the other 3 each takes 4 secs, then instead of waiting for 2+4+4+4 seconds, i'd only be waiting for 4?
If this is the case, how do i then guarantee not to return the Category object before all of the calls are complete, so that i can be sure that all data has been filled before serving it?

Answer 3

User475983607 posted

You mean that If i have an object being populated, for instance, a category of some sort, and this category requires 1 initial db call to get its main details, and then 3 other, 1 to get the associated media, another to get the associated products, and finally one to list the associated news items, i could benefit by using the openAsync, allowing me to be processing all the requests 'simultaneously' instead of having them wait for the first to finish before starting the second one, and so on? So, if the first call takes 2secs, and the other 3 each takes 4 secs, then instead of waiting for 2+4+4+4 seconds, i'd only be waiting for 4?
If this is the case, how do i then guarantee not to return the Category object before all of the calls are complete, so that i can be sure that all data has been filled before serving it?

The link in my first post illustrates asynchronous operations using a preparing breakfast analogy.  I'm not sure if you read the link or not but the link does a very good job of explaining what asynchronous means.  Then apply the concept to threads on a web server.

I believe your example breaks down as

await OpenAsync (1 sec)
await Get Main record (2 sec)
   Get products async (3 sec)
   Get media async (4 sec)
   Get news async (5 sec)
await the 3 Gets
await CloseAsync

Total time calculation is 1 + 2 + 5 = 8 sec

 

Answer 4

User1738843376 posted

Thx mgebhard,

I had read the link you provided, and along with your additional explanation. i believe i got the point.

Thanks again