3

(The title looks similar to SqlConnection.Open vs SqlConnection.OpenAsync - what's different between the two beyond the obvious? but a. It doesn't has any answer, and b. the observations in my case are different)

While bench-marking to chase a performance issue in an asp.net service, I accidentally hit a queer observation where SqlConnection.Open performs far better than SqlConnection.OpenAsync(more details below).

From the source code here looks like the code of Open is just a subset of OpenAsync. Diff here

I am curious on given Open does exactly same things as OpenAsync, what is the advantage of using the async version(if any)?

Details:

This is how I create the connection

using (SqlConnection connection = new SqlConnection(connectionString))
{
    await connection.OpenAsync();
    // run actual operation on connection (in an async way)
}

This code block takes 13 seconds on an average for 100 concurrent calls. I have tried increasing the thread pool count to 200 to reduce chances of tasks waiting for threads. The observation doesn't change even after letting the default threadpool count.

The interesting part is if I change await connection.OpenAsync() with connection.Open() the performance improves 10 times. 1.01 seconds on an average of 100 concurrent calls.

I tried playing around with connection pool settings and looks like even if I reduce it to an arbitrarily low number (2 is what tested with) the number doesn't change much.

The driver code is:

Task.WhenAll(Enumerable.Range(0, maxConcurrentCalls).Select((idx) => CallQuery(idx))).Result;

The reason I do a wait (by doing a .Result is because the driver code is a console app which has to have a sync main). In the actual production code everything including the controller is async.

Community
  • 1
  • 1
bashrc
  • 4,725
  • 1
  • 22
  • 49
  • 1
    `async` doesn't make anything faster. In fact it creates some overhead which will often make it slower. It just releases the thread for other operations while it waits for something (such as IO or DB) to finish. – smoksnes Jan 12 '17 at 09:28
  • @smoksnes I am aware of that. What I want to mean is that typically you would want to have an async equivalent when your sync method is heavy. So you off load to a seperate thread. Atleast from the code it doesn't look like openasync does anything less than open – bashrc Jan 12 '17 at 09:33
  • I see. I got confused by your performance measurements. However, if you read the code there are one difference. `OpenAsync` passes `TaskCompletionSource ` to `TryOpen`. Which eventually comes to [`TryGetConnection`](https://referencesource.microsoft.com/#System.Data/System/Data/ProviderBase/DbConnectionFactory.cs,bdc3904ec236171d) which uses threading. – smoksnes Jan 12 '17 at 09:45
  • Could you share all your code you used to measure this? Are you by any chance waiting synchronously for your `async` method to complete? – svick Jan 12 '17 at 18:10
  • @svick I am measuring it in two ways. One, I have a service deployed on PaaS which is purely async (uses async controllers etc). I make calls to that endpoint and measure the stats. I see similar results as in the question. I am adding the second approach to the question which uses a console app to measure the above data. – bashrc Jan 12 '17 at 18:39
  • Do you think it could be related to my question : http://stackoverflow.com/q/42433948/1826096 – François Feb 25 '17 at 22:16

1 Answers1

-3

One way I use OpenAsync is if I have open connections to more than one database.

Task t = conn1.OpenAsync();
conn2.Open();
t.Wait();

If a have slow connection to a remote server, the Open can take a couple seconds. This way the two Open's are happening in parallel.

JoeLeBaron
  • 338
  • 1
  • 3
  • 12
  • 1
    On top of [blocking on async code](https://blog.stephencleary.com/2012/07/dont-block-on-async-code.html), this is extra messy and has nothing to do with the question. The correct way to open two connections from the same place at once is `await Task.WhenAll(conn1.OpenAsync(), conn2.OpenAsync())`. – GSerg Sep 26 '20 at 15:51