我应该在以下案例实例中使我的SQL查询异步吗？

To summarize my issue: I have to make 3 separate calls to 3 different endpoints. I have to do this programatically through a large list of records. Two of the calls are made using asynchronous HTTP requests to two different in which I use aysnc await. The third call I have to make is a query to a MS SQL database that does not have an API wrapper. The two calls to the APIs take ~22 seconds each and the query to the database takes ~2 seconds. Individually this is fine, however when I make the MS SQL query part of the method chain, the entire thing becomes synchronous. And where I would have originally saved the user a lot of time doing simultaneous queries to the two APIs, my app will wait for these queries to be completed before attempting the next call to the database.

总结一下我的问题：我必须对3个不同的端点进行3次单独调用。我必须通过大量记录以编程方式执行此操作。其中两个调用是使用异步HTTP请求到两个不同的，我使用aysnc await。我要做的第三个调用是对没有API包装器的MS SQL数据库的查询。对API的两次调用每次需要大约22秒，对数据库的查询需要大约2秒。单独这是好的，但是当我将MS SQL查询作为方法链的一部分时，整个事情变得同步。而且我最初会将用户花费大量时间同时查询两个API，我的应用程序将在尝试下次调用数据库之前等待这些查询完成。

I have previously been told that database queries are transactional and cannot be made asynchronous. I am wondering if this is true and I can actually make a truly asynchronous call to the database.

我之前被告知数据库查询是事务性的，不能异步。我想知道这是否属实，我实际上可以对数据库进行真正的异步调用。

My other option is to pre-populate a List<T> of objects that I can create from the series of SQL queries to the database when the job is executed. However my worry is the potential number of objects stored in this List, whether it will be in danger of overloading memory for certain jobs that require hundreds of thousands of records to be processed, or possibly the speed of searching through the objects to pull the correct values.

我的另一个选择是在执行作业时预先填充我可以从一系列SQL查询创建的对象的List 到数据库。但是我担心的是这个列表中存储的对象的潜在数量，是否存在需要处理数十万条记录的某些作业的内存过载的危险，或者可能是搜索对象以获取正确内容的速度值。

1 个解决方案

#1

when I make the MS SQL query part of the method chain, the entire thing becomes synchronous.

当我将MS SQL查询作为方法链的一部分时，整个事物变得同步。

That is an incorrect implementation. You can most certainly mix synchronous and asynchronous calls inside a single function.

这是一个不正确的实现。您当然可以在单个函数中混合同步和异步调用。

Let's say you have these three methods:

假设您有以下三种方法：

async Task<Result1> FirstQueryAsync() {
    ...
}
async Task<Result2> SecondQueryAsync() {
    ...
}
Result3 QueryDb() {
}

You can write this:

你可以这样写：

async Task<OverallResult> QueryThreeSources() {
    // Fire off async queries
    var firstTask = FirstQueryAsync().ConfigureAwait(false);
    var secondTask = SecondQueryAsync().ConfigureAwait(false);
    // Do synchronous query
    var third = QueryDb();
    // Await the results of the other two
    var first = await firstTask;
    var second = await secondTask;
    return Combine(first, second, third);
}

I have previously been told that database queries are transactional and cannot be made asynchronous.

我之前被告知数据库查询是事务性的，不能异步。

This is incorrect. Transactional nature of querying manifests itself on the RDBMS side, inside RDBMS. This is completely independent from the decision of your program to receive its data synchronously or asynchronously. .NET provides asynchronous APIs for accessing SQL Server, so you can definitely rewrite your DB code using asynchronous style.

这是不正确的。查询的事务性质在RDBMS内部显示在RDBMS端。这完全独立于程序同步或异步接收数据的决定。 .NET提供了用于访问SQL Server的异步API，因此您绝对可以使用异步样式重写数据库代码。

My other option is to pre-populate a List<T> of objects that I can create from the series of SQL queries to the database when the job is executed.

我的另一个选择是在执行作业时预先填充我可以从一系列SQL查询创建的对象的List 到数据库。

List option would be good only if you query the same data multiple times. If all your queries tend to bring different subsets of the data, which are small in comparison to the overall data stored in the table that you query, caching the data in List<T>s is usually counterproductive.

仅当您多次查询相同数据时，列表选项才会有效。如果所有查询都倾向于带来不同的数据子集，这些子集与您查询的表中存储的整体数据相比较小，则缓存List 中的数据通常会适得其反。

#1