Ndtyjky

I am in the process of making all of my database operations asynchronous. However, I don't seem to get the desired results of asynchrony.

e.g. -

I am inserting 100 rows to a table in a database in a loop. The function that does this is a async function. It doesn't seem to yield control to its caller when its awaiting. The code is below -

[HttpGet]

[Route("/api/logtest")]

public async Task<JObject> logTestAsync()

{

    JObject retval = new JObject();

    DateTime dt1 = DateTime.Now;



    Task t = BulkLogInsertAsync();





    DateTime dt2 = DateTime.Now;

    retval["Exec_Time"] = dt2.Subtract(dt1).TotalMilliseconds;



    await t;



    DateTime dt3 = DateTime.Now;

    retval["Await_Time"] = dt3.Subtract(dt2).TotalMilliseconds;

    return retval;

}



private async Task BulkLogInsertAsync()

{

    List<Task<int>> allTasks = new List<Task<int>>();

    for (int i = 0; i<100;i++)

    {

         allTasks.Add(LogInsertAsync("insert into logs values (getdate() , 'custom' , 'sample message. ', 'Log.bills', 'callsite1', '', '', '')"));

        //allTasks.Add(LogInsertAsync("WAITFOR DELAY '00:00:02';"));

    }

    await Task.WhenAll(allTasks.ToArray()).ConfigureAwait(false);

}

private async Task<int> LogInsertAsync(string cmd)

{

    int res = 0;

    using (SqlConnection hookup = new SqlConnection(@"[mycnstr]"))

    {

        Task connectionOpeningTask = hookup.OpenAsync();

        using (SqlCommand sqlcmd = new SqlCommand(cmd, hookup))

        {

            await connectionOpeningTask.ConfigureAwait(false);

            res = await sqlcmd.ExecuteNonQueryAsync().ConfigureAwait(false);

        }

        hookup.Close();

    }

    return res;

}

When I call the API /api/logtest the very first time, I seem to get desired results with exec_time much less than await_time (0.2s vs 4s)

However from 2nd run onwards I get await_time much less than exec_time (4s vs 0.2s) which is making me believe code ran synchronously.

Also the same code in a console app using .net framework 4.6.1, gives desired results continuously. and no I did not restart the console app. Ran the BulkLogInsertAsync in a do while loop :-)

Can anybody please tell me where I am going wrong ?

Let's set a couple of things straight:

1. Threads are not magical resources that are just laying there, w8ing for work


2. The higher number of threads you have, more resources you´ll need to manage them


3. Asynchrony, although very helpful is not an easy thing

Ok, first of all, your app starts with a predefined number of w8ing threads (usually number of cores*2, if i recall correctly, can find the damn msdn article). If you request threads and you´re using under this limit, you get them instantaneously, otherswise, you'll have to w8 500ms (cant find the article)

Second, async is not parallel!

Third, in your code, whenever you use await, it awaits! so, i suggest you refactor await on OpenAsync, chaining with ContinueWith and only use the await once in your code, because, as it is right now, your using's are preventing async from working properly... you will have to manage the connections and disposals on your own if you want the code to run asynchronously

Edit1:

There are 2 types of threads: IO Threads and Worker threads. In your code, what you need is IO threads to w8 for the database... if you need more context on this, you can start here