Async calls are executing sequentially-CodePudding

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

CodePudding user response：

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
}

CodePudding user response：

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.