With Task.WhenAll(), tasks are not completed concurrently

Question

I have to make 10 database calls to get 10 entries based on different parameters. I'm using Task.WhenAll to avoid running them in series, but the runtime indicates they're effectively still in series. Ie. a single entry takes 100ms to pull. Ten entries takes 1000ms. The time is unchanged from awaiting them in series. I've simplified, but will try to capture the essence of the structure. I've also removed my business logic and data structure.

private async Task<IEnumerable<Entry>> GetEntriesAsync(List<long> entryIds)
{
    var tasks = new List<Task<Entry>>();

    foreach (var entryId in entryIds)
    {
        var task = this.GetEntryAsync(entryId);
        tasks.Add(task);
    }

    await Task.WhenAll(tasks);

    var entries = tasks.Select(x => x.Result).ToList();

    return thumbnails;
}

I'd expect this to run in about the same amount of time regardless of the length of entryIds. Instead it scales in time taken with the number of entries.

Next question is what does GetEntryAsync actually do? It instantiates a DbContext, and then runs a Select against IQueryable in that context. The contexts are instantiated for each run through the loop (ie 10 entries = 10 contexts), that way I can make the multiple database requests concurrently.

public virtual async Task<Entry> GetEntryAsync(long entryId) 
{ 
    // unitOfWorkFactory.Create() instantiates a repository with a dbcontext
    // instantiated using DBContextFactory.CreateDBContext()
    var entryRepo = this.unitOfWorkFactory.Create().GetWordsRepository(language);

    Entry entry = await entryRepo.GetEntryAsync(entryId);

    return Entry;
}

Within the entryRepo class I have:

public async Task<Entry> GetEntryAsync(long entryId) 
{ 
    // Entries is a DBSet<Entry>
    IQueryable<Vocabulary> entries = this.dbContext.Entries
                                                   .AsNoTracking()
                                                   .Where(x => x.Id == entryId);

    var entry = await this.GetEntryAsync(entries)

    return entry;
}

private async Task<Entry> GetEntryAsync(IQueryable<Entry> entries) 
{ 
    return await entries.Select(x => new Entry()
                                         {
                                             Id = x.Id
                                         }).FirstOrDefaultAsync();
 }

I need the query to run closer to 100ms rate. I've also tried using the Select method and Parallel.ForEachAsync as described here: Parallel foreach with asynchronous lambda. All have the same result.

I've put console writes in my functions, so I know the tasks are all starting before the first finishes. So the tasks are not actually in series, but for some reason the time taken is as if they are in series.

Edit: important clarification: The where clause is not based on just an entryId, and entryId is not the key Id in the table I'm hitting. There can be multiple rows with that entryId. I'm also using several more parameters to get the specific entry (language, version, secondaryId1, secId2, secId3, groupNumber, type, etc).

I'm not sure how to write a single query because, I need say 10 entries where each have a different combination of the where parameters.

Answer 1

Parallelization might not increase performance in this case. Like @Charlieface said. Databases don't like that. Querying all entities in one query however helps a lot. If the amount of entryIds you want to query is small. (say: less than 100) using List.Contains() can help a lot.

private async Task<List<Entry>> GetEntriesAsync(List<long> entryIds)
{
    return await this.dbContext.Entries.AsNoTracking()
        .Where(x => entryIds.Contains(x.Id))
        .Select(x => new Entry()
        {
            Id = x.Id
        }).ToListAsync();
}

Linq sends this to SQL Server in an optimized way, because it understands what List.Contains() does. This doesn't work for all methods, but Contains() is special.

Look out for IQueryable methods that starts with 'To' Like ToList() or ToDictionary(). They cause the query to be executed and pull the result in memory. So Calling First() or FirstOrDefaultAsync() after them does nothing for performance.

That's why I choose to use the async version of ToList(), since that is where the querying happens.

Answer 2

Remove ToList in GetEntryAsync:

 private async Task<Entry> GetEntryAsync(
        IQueryable<Entry> entries) 
        { 
            return await entries.Select(x => new Entry()
                {
                    Id = x.Id
                }).FirstOrDefaultAsync();
        }

Not only you are making method syncronious but you also fetch everything into memory while you need only 1 entry.

But in general you should just write query so it returns all needed entries.