CODEWITHSUNDEEP
c#asynchronous.net-coreasync-await
venkatesh k
venkatesh k

Reputation: 59

Async calls are executing sequentially

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();
    }
}

Upvotes: 0

Views: 616

Answers (3)

Stephen Cleary
Stephen Cleary

Reputation: 457057

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.

Upvotes: 1

tmaj
tmaj

Reputation: 35105

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:

  1. See Parameterize an SQL IN clause
  2. 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)"

  3. Use Entity Framework and then LINQ Where in collection clause.

Upvotes: 0

Mihail
Mihail

Reputation: 823

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
}

Upvotes: 0

Related Questions