Async calls are executing sequentially

Question

I have an async database call inside a loop. But the database calls are happening in a sync way. I am not sure the calls are not happening in an async fashion. Any directions on what is wrong with the below code.

static async Task Main(string[] args)
{
      long executionTime = 0;
      await SequentialDBCallAsync();
}

private static async Task SequentialDBCallAsync()
{
    DBLayer dB = new DBLayer();

    for (int i = 0; i < 10; i++)
    {
        var product = await dB.GetDataByNameAsync("Name - 100000");
        Console.WriteLine(i);
    }
}

public async Task<Product> GetDataByNameAsync(string name)
{
    using (var sql = new SqlConnection("Data Source=(localdb)\\ProjectsV13;Initial Catalog=Inventory;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"))
    {
        sql.Open();
        string query = "Select id, Name from Product where Name = @name";

        SqlCommand cmd = new SqlCommand(query, sql);
        cmd.Parameters.Add("@name", System.Data.SqlDbType.VarChar).Value = name;

        Console.WriteLine("started");
        var reader = await cmd.ExecuteReaderAsync();
        Console.WriteLine("executing");

        while (await reader.ReadAsync())
        {
            var product = new Product()
            {
                Id = reader.GetInt32(0),
                Name = reader.GetString(1)
            };

            return product;
        }

        return new Product();
    }
}

Try not awaiting each call, add the `Tasks` to a list and then call `Task.WaitAll`. — John, Aug 15 '22 at 09:10
what behaviour do you expect that is different to the behaviour you have with the code given? — monty, Aug 15 '22 at 09:10
You're `await`ing the asynchronous calls which pauses execution of code until the `async` method has returned. — phuzi, Aug 15 '22 at 09:10
prefer awaiting [`Task.WhenAll`](https://learn.microsoft.com/en-us/dotnet/api/system.threading.tasks.task.whenall?view=net-6.0) — Jodrell, Aug 15 '22 at 09:21
https://stackoverflow.com/questions/58825715/how-to-return-and-consume-an-iasyncenumerable-with-sqldatareader — Jodrell, Aug 15 '22 at 09:27
The `SequentialDBCallAsync` returns a `Task`. Do you really want to ignore all the `var product` that you fetch from the DB? — Theodor Zoulias, Aug 15 '22 at 09:38
*"I am not sure the calls are not happening in an async fashion."* -- You might want to change the terminology from sync/async to sequentially/concurrently. I assume that the desirable behavior is to invoke all the asynchronous methods concurrently. The question currently is not clear regarding the desirable behavior. — Theodor Zoulias, Aug 15 '22 at 09:43
Aside from the fundamental issue of `async`/`await` not being what you think it is, note that SQL Server is already quite good at using parallelism within queries and firing off queries in parallel often quickly has diminishing returns. When you are parallelizing many very small DB operations on the client end, it often pays off to instead start grouping these there and sending them over in one go (through things like table-valued parameters and bulk inserts). Of course there are still legitimate scenarios (like separate user sessions) but it's still worth keeping in mind. — Jeroen Mostert, Aug 15 '22 at 09:45
@JeroenMostert, especially if the data may be changing between calls. — Jodrell, Aug 15 '22 at 09:56
Thank you for pointing out that I should add the tasks to a list and wait for all the tasks to complete. I was testing a code snippet to understand behavior with async/await, parallel for, and combinations. Other comments like leaving out the product data SQL server parallel behavior were not my focus. Thank you for your time. — venkatesh k, Aug 15 '22 at 10:43
Side note: `reader` needs a `using`, as does `cmd`. I agree you should not use parallel queries, instead batch them up and get them from the server in one go. Parallelism doesn't work well on databases, as they tend to be IO-bound. — Charlieface, Aug 15 '22 at 10:47
Does this answer your question? [Why is this different? Async running sequentially vs parallel](https://stackoverflow.com/questions/68742841/why-is-this-different-async-running-sequentially-vs-parallel) — Charlieface, Aug 15 '22 at 10:49

score 1 · Answer 1 · answered Aug 15 '22 at 13:47

Async calls are executing sequentially ... But the database calls are happening in a sync way.

Your first statement was correct; the second one was incorrect.

await is often used to make asynchronous calls execute sequentially (one at a time). They are still asynchronous, though; they're not synchronous. Indeed, the whole point of async/await is for control flow to remain natural while being asynchronous instead of synchronous.

For most code, you can use asynchronous concurrency (e.g., Task.WhenAll). This doesn't work as well for databases, though, because only one query can be active per db connection. This is a limitation of the on-the-wire database protocols (although nothing prevents a future database from removing that limitation). So, in order to use a Task.WhenAll-based solution, you'd have to create that many database connections. While occasionally useful, this is not always a good idea.

Instead, what you want to do is restructure your query itself so that it does all the work in a single query. Instead of running a N queries that each retrieve a single row, run a single query that returns N rows.

Mihail · Answer 2 · 2022-08-15T09:18:43.747

0

Your current code awaits for each invocation to finish, i.e., it does not proceed to the next for cycle until dB.GetDataByNameAsync does not finish.

If you want them to execute at the same time you should have something like this:

var tasks = Enumerable.Range(0, 10).Select(i => dB.GetDataByNameAsync("Name - 100000")).ToList();
await Task.WhenAll(tasks);

It will create all the tasks and only then it will wait for all of them to finish.

However, you can also process them while they are returning, like so:

while (tasks.Any())
{
    var finishedTask = await Task.WhenAny(tasks);
    tasks.Remove(finishedTask);
    
    // process task
}

edited Aug 15 '22 at 09:18

answered Aug 15 '22 at 09:13

Mihail

730
4
17

@JonasH added a small phrase for that :) – Mihail Aug 15 '22 at 09:19
Thank you for pointing out that I should add the tasks to a list and wait for all the tasks to completed. – venkatesh k Aug 15 '22 at 10:40
2

While a good answer in the general case and does answer the premise of the question, this is likely to be a really bad idea to do against a database. – Charlieface Aug 15 '22 at 10:46

score 0 · Answer 3 · answered Aug 15 '22 at 13:26

While Task.WhenAll (or even better Parallel.ForEachAsync (twitter link)) will execute the tasks concurrently, getting records one by one is very inefficient.

The signature of the method should be something like this:

public async Task<List<Product>> GetDataByNameAsync(IEnumerable<string> names)

and it should perform a single query: SELECT id, Name FROM Product WHERE Name IN (...,...,...).

In order to use WHERE Name IN in the code you can:

See Parameterize an SQL IN clause
Use Dapper, please see List support (I shortened the original example queries):

For example:

connection.Query<int>("select * from ... where Id in @Ids", new { Ids = new int[] { 1, 2, 3 } });

Will be translated to:

select * from ... where Id in (@Ids1, @Ids2, @Ids3)"
Use Entity Framework and then LINQ Where in collection clause.

Async calls are executing sequentially

3 Answers3