asp.net - ASP.Net MVC 中长时间运行 SQL 查询的最佳实践

Question

我有一个操作方法，需要根据用户选择的日期完成15~52个长时间运行的SQL查询(所有这些查询都很相似，每个需要5秒以上才能完成)。

经过大量研究后，似乎在不阻塞 ASP.Net 线程的情况下执行此操作的最佳方法是使用带有 SQL 查询的 async/await 任务方法:

[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;
}

进行此更改后，完成此操作所需的时间大大减少，因为现在我能够同时执行多个(15~52)个异步 SQL 查询并等待它们完成，而不是顺序运行它们。然而，用户开始遇到很多超时问题，例如:

(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"

是否是线程饥饿造成的？我有一种感觉，我可能使用了线程池中的太多线程来实现我想要的，但我认为这应该不是问题，因为我使用了 async/await 来防止所有线程被阻塞。

如果事情不能以这种方式工作，那么执行多个长时间运行的 SQL 查询的最佳实践是什么？

Answer 1

考虑限制正在执行的并发任务数量，例如:

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

请注意我更改了您的 taskList到实际的List<Task> ，它似乎更容易使用，因为我们需要从列表中添加/删除任务。

此外，您应该在清除 taskList 之前获取结果，因为您稍后将使用它们。