Best practice for long running SQL queries in ASP.Net MVC

Question

I have an action method which needs to complete 15~52 long running SQL queries (all of them are similar, each takes more than 5 seconds to complete) according to user-selected dates.

After doing a lot of research, it seems the best way to do this without blocking the ASP.Net thread is to use async/await task methods With SQL Queries:

[HttpPost]
public async Task<JsonResult> Action() {   
    // initialization stuff

    // create tasks to run async SQL queries
    ConcurrentBag<Tuple<DateTime, List<long>>> weeklyObsIdBag = 
        new ConcurrentBag<Tuple<DateTime, List<long>>>();
    Task[] taskList = new Task[reportDates.Count()];
    int idx = 0;
    foreach (var reportDate in reportDates) { //15 <= reportDates.Count() <= 52
        var task = Task.Run(async () => {
            using (var sioDbContext = new SioDbContext()) {
                var historyEntryQueryable = sioDbContext.HistoryEntries
                    .AsNoTracking()
                    .AsQueryable<HistoryEntry>();
                var obsIdList = await getObsIdListAsync(
                    historyEntryQueryable, 
                    reportDate
                );
                weeklyObsIdBag.Add(new Tuple<DateTime,List<long>>(reportDate, obsIdList));
            }
        });
        taskList[idx++] = task;
    }
    //await for all the tasks to complete
    await Task.WhenAll(taskList);

    // consume the results from long running SQL queries, 
    // which is stored in weeklyObsIdBag
}

private async Task<List<long>> getObsIdListAsync(
    IQueryable<HistoryEntry> historyEntryQueryable, 
    DateTime reportDate
) {
    //apply reportDate condition to historyEntryQueryable

    //run async query
    List<long> obsIdList = await historyEntryQueryable.Select(he => he.ObjectId)
        .Distinct()
        .ToListAsync()
        .ConfigureAwait(false);
    return obsIdList;
}

After making this change, the time taken to complete this action is greatly reduced since now I am able to execute multiple (15~52) async SQL queries simultaneously and await for them to complete rather than running them sequentially. However, users start to experience lots of time out issues, such as :

(from Elmah error log) 
"Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. 
 This may have occurred because all pooled connections were in use and max pool size was 
 reached."
"The wait operation timed out"

Is it caused by thread starvation? I got a feeling that I might be using too many threads from thread pool to achieve what I want, but I thought it shouldn't be a problem because I used async/await to prevent all the threads from being blocked.

If things won't work this way, then what's the best practice to execute multiple long running SQL queries?

Answer 1

Consider limiting the number of concurrent tasks being executed, for example:

int concurrentTasksLimit = 5;
List<Task> taskList = new List<Task>();
foreach (var reportDate in reportDates) { //15 <= reportDates.Count() <= 52
    var task = Task.Run(async () => {
        using (var sioDbContext = new SioDbContext()) {
            var historyEntryQueryable = sioDbContext.HistoryEntries
                .AsNoTracking()
                .AsQueryable<HistoryEntry>();
            var obsIdList = await getObsIdListAsync(
                historyEntryQueryable, 
                reportDate
            );
            weeklyObsIdBag.Add(new Tuple<DateTime,List<long>>(reportDate, obsIdList));
        }
    });
    taskList.Add(task);
    if (concurrentTasksLimit == taskList.Count)
    {
        await Task.WhenAll(taskList);
        // before clearing the list, you should get the results and store in memory (e.g another list) for later usage...
        taskList.Clear();
    }
}
//await for all the remaining tasks to complete
if (taskList.Any())
    await Task.WhenAll(taskList);

Take note I changed your taskList to an actual List<Task>, it just seems easier to use it, since we need to add/remove tasks from the list.

Also, you should get the results before clearing the taskList, since you are going to use them later.