如何使两个SQL查询真正异步 [英] How to make two SQL queries really asynchronous

问题描述

我的问题是基于实际的项目问题，但是我从未使用过System.Threading.Tasks库或执行过任何涉及线程的严肃编程，因此我的问题可能是缺乏对特定库的知识和对什么内容的更普遍的误解的混合异步确实意味着编程.

My problem is based on a real project problem, but I have never used the System.Threading.Tasks library or performing any serious programming involving threads so my question may be a mix of lacking knowledge about the specific library and more general misunderstanding of what asynchronous really means in terms of programming.

所以我的现实情况是这样-我需要获取有关用户的数据.在我当前的情况下，这是财务数据，所以可以说，我需要某个用户使用所有Accounts，所有Deposits和所有Consignations.在我的情况下，这意味着要查询每个属性的一百万条记录，并且每个查询本身都相对较慢，但是获取Accounts的速度要比获取Deposits的速度慢几倍.因此，我为将要使用的三种银行产品定义了三个类，当我要获取特定用户的所有银行产品的数据时，我会执行以下操作:

So my real world case is this - I need to fetch data about an user. In my current scenario it's financial data so let say I need all Accounts, all Deposits and all Consignations for a certain user. In my case this means to query million of records for each property and each query is relatively slow itself, however to fetch the Accounts is several times slower than fetching the Deposits. So I have defined three classes for the three bank products I'm going to use and when I want to fetch the data for all the bank products of certain user I do something like this :

List<Account> accounts = GetAccountsForClient(int clientId);
List<Deposit> deposits = GetDepositsForClient(int clientId);
List<Consignation> consignations = GetConsignationsForClient(int clientId);

所以问题开始于此，我需要同时获取所有这三个列表，因为我要将它们传递给显示所有用户数据的视图.但是，现在执行是同步的(如果我在此处正确使用该术语)，因此收集所有三种产品的数据的总时间为:

So the problem starts here I need to get all those three list at the same time, cause I'm going to pass them to the view where I display all users data. But as it is right now the execution is synchronous (If I'm using the term correctly here) so the total time for collecting the data for all three products is:

Total_Time = Time_To_Get_Accounts + Time_To_Get_Deposits + Time_To_Get_Consignations

这不是很好，因为每个查询都相对较慢，因此总时间相当长，而且accounts查询所花的时间比其他两个查询要多得多，所以今天引起我注意的想法是- 如果我可以同时执行此查询该怎么办".也许这是我对该主题的最大误解，但对我而言，最接近这个主意的是使它们异步，因此也许Total_Time不会是最慢查询的时间，但会比这三个查询的总和快得多查询.

This is not good because the each query is relatively slow so the total time is pretty big, but also, the accounts query takes much more time than the other two queries so the idea that get into my head today was - "What if I could execute this queries simultaneously". Maybe here comes my biggest misunderstanding on the topic but for me the closest to this idea is to make them asynchronous so maybe then Total_Time won't be the time of the slowest query but yet will be much faster than the sum of all three queries.

由于我的代码很复杂，所以我创建了一个简单的用例，我认为这反映了我试图做的很好.我有两种方法:

Since my code is complicated I created a simple use case which I think, reflect what I'm trying to do pretty well. I have two methods :

public static async Task<int> GetAccounts()
{
    int total1 = 0;
    using (SqlConnection connection = new SqlConnection(connString))
    {
        string query1 = "SELECT COUNT(*) FROM [MyDb].[dbo].[Accounts]";
        SqlCommand command = new SqlCommand(query1, connection);
        connection.Open();
        for (int i = 0; i < 19000000; i++)
        {
            string s = i.ToString();
        }
        total1 = (int) await command.ExecuteScalarAsync();
        Console.WriteLine(total1.ToString());
    }
    return total1;
}

和第二种方法:

public static async Task<int> GetDeposits()
{
    int total2 = 0;
    using (SqlConnection connection = new SqlConnection(connString))
    {
        string query2 = "SELECT COUNT(*) FROM [MyDb].[dbo].[Deposits]";
        SqlCommand command = new SqlCommand(query2, connection);
        connection.Open();
        total2 = (int) await command.ExecuteScalarAsync();
        Console.WriteLine(total2.ToString());
    }
    return total2;
}

我这样称呼:

static void Main(string[] args)
{
    Console.WriteLine(GetAccounts().Result.ToString());

    Console.WriteLine(GetDeposits().Result.ToString());
}

如您所见，我首先调用GetAccounts()，我故意降低执行速度，因此有机会继续执行下一个方法.但是，在一段时间内没有得到任何结果，然后同时在控制台上打印了所有内容.

As you can see I call GetAccounts() first and I slow the execution down on purpose so I give a chance the execution to continue to the next method. However I'm not getting any result for a certain period of time and then I get all printed on the console at the same time.

问题是-如何制作，以便我不等第一种方法完成后才转到下一种方法.通常，代码结构不是那么重要，我真正想弄清楚的是，是否有任何方法可以使两个查询同时执行.这里的样本是我研究的结果，可能会扩展到我将获得期望结果的程度.

So the problem - how to make so that I don't wait for the first method to finish, in order to go to the next method. In general the code structure is not that important, what I really want to figure out is if there's any way to make both queries to execute at the same time. The sample here is the result of my research which maybe could be extended to the point where I'll get the desired result.

P.S 我之所以使用ExecuteScalarAsync();只是因为我从使用它的方法开始.实际上，我将使用Scalar和Reader.

P.S I'm using ExecuteScalarAsync(); just because I started with a method which was using it. In reality I'm gonna use Scalar and Reader.

推荐答案

在尚未完成的任务上使用Result属性时，调用线程将阻塞，直到操作完成.这意味着在您的情况下，GetAccounts操作需要在对GetDeposits的调用开始之前完成.

When you use the Result property on a task that hasn't completed yet the calling thread will block until the operation completes. That means in your case that the GetAccounts operation need to complete before the call to GetDeposits starts.

如果要确保这些方法是并行的(包括同步的CPU密集型部件)，则需要将该工作分流到另一个线程.最简单的方法是使用Task.Run:

If you want to make sure these method are parallel (including the synchronous CPU-intensive parts) you need to offload that work to another thread. The simplest way to do so would be to use Task.Run:

static void Main(string[] args)
{
    var accountTask = Task.Run(async () => Console.WriteLine(await GetAccounts()));
    var depositsTask = Task.Run(async () => Console.WriteLine(await GetDeposits()));

    Task.WhenAll(accountTask, depositsTask).Wait();
}

---

由于Main不能为async，因此不能使用await，因此您可以简单地调用此方法并使用Wait同步等待其完成.

---

Because Main can't be async and so can't use await you can simply call this method and synchronously wait for it to complete using Wait.

这篇关于如何使两个SQL查询真正异步的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！