如何正确进行异步/并行数据库调用 [英] How to properly make asynchronous / parallel database calls

问题描述

我正在寻找处理多个数据库调用的适当方法，这些方法可能会受益于同时运行.这些查询只是针对存储过程，这些存储过程使用在ASP.NET MVC应用程序中以编程方式组装到DataTables中的数据进行插入或合并.

I'm looking for the proper way to handle multiple database calls that would likely benefit from running simultaneously. The queries are just to stored procedures that are either doing inserts or merges using data that is programmatically assembled into DataTables in my ASP.NET MVC app.

当然，我已经看到了有关async和await的一些信息，这似乎是我需要做的，但是我对如何实现它没有清晰的了解.一些信息表明，这些调用仍将是顺序的，并且一个仍在等待另一个完成.这似乎毫无意义.

Of course I have seen some information on async and await, and that appears to be what I would need to do, but I don't have a clear understanding of how to implement it. Some information is saying that the calls would still be sequential, and that one would still be waiting on another to complete. That seems pointless.

最终，我想要一个解决方案，该解决方案允许我在花费最长的时间才能完成的时间内运行所有查询.我希望所有查询也返回受影响的记录数(就像现在一样).

Ultimately, I would like a solution that allows me to run all the queries in the time it takes for the longest procedure to complete. I would like all the queries to return the number of records affected (as they do now) as well.

这是我现在正在做的(绝不是并行的):

Here is what I have going on now (which is in no way parallel):

// Variable for number of records affected
var recordedStatistics = new Dictionary<string, int>();

// Connect to the database and run the update procedure
using (var dbc = new SqlConnection(db.Database.Connection.ConnectionString))
{
    dbc.Open();

    // Merge One procedure
    using (SqlCommand cmd = new SqlCommand("MergeOneProcedure", dbc))
    {
        // 5 minute timeout on the query
        cmd.CommandTimeout = 300;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@TVP", MergeOneDataTable);

        // Execute procedure and record the number of affected rows
        recordedStatistics.Add("mergeOne", cmd.ExecuteNonQuery());
    }

    // Merge Two procedure
    using (SqlCommand cmd = new SqlCommand("MergeTwoProcedure", dbc))
    {
        // 5 minute timeout on the query
        cmd.CommandTimeout = 300;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@TVP", MergeTwoDataTable);

        // Execute procedure and record the number of affected rows
        recordedStatistics.Add("mergeTwo", cmd.ExecuteNonQuery());
    }

    // Merge Three procedure
    using (SqlCommand cmd = new SqlCommand("MergeThreeProcedure", dbc))
    {
        // 5 minute timeout on the query
        cmd.CommandTimeout = 300;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@TVP", MergeThreeDataTable);

        // Execute procedure and record the number of affected rows
        recordedStatistics.Add("mergeThree", cmd.ExecuteNonQuery());
    }

    // Merge Four procedure
    using (SqlCommand cmd = new SqlCommand("MergeFourProcedure", dbc))
    {
        // 5 minute timeout on the query
        cmd.CommandTimeout = 300;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@TVP", MergeFourDataTable);

        // Execute procedure and record the number of affected rows
        recordedStatistics.Add("mergeFour", cmd.ExecuteNonQuery());
    }

    // Merge Five procedure
    using (SqlCommand cmd = new SqlCommand("MergeFiveProcedure", dbc))
    {
        // 5 minute timeout on the query
        cmd.CommandTimeout = 300;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@TVP", MergeFiveDataTable);

        // Execute procedure and record the number of affected rows
        recordedStatistics.Add("mergeFive", cmd.ExecuteNonQuery());
    }

    dbc.Close();
}

return recordedStatistics;

所有这些代码都在为DataTables组装数据的同一方法内.我对async的有限理解使我相信我需要将前面的代码提取到其自己的方法中.然后，我将调用该方法并返回await.但是，我什至不了解它.

All of that code is within the same method that assembles the data for the DataTables. My limited understanding of async would lead me to believe that I would need to extract the previous code into its own method. I would then call that method and await the return. However, I don't even know enough about it to begin.

我以前从未做过任何异步/并行/多线程编码.这种情况只是让我觉得现在是个完美的时机.也就是说，我想学习最好的方法，而不必学习错误的方法.

I have never done any asynchronous/parallel/multithreaded coding before. This situation just makes me feel like it is the perfect time to jump in. That said, I would like to learn the best way, instead of having to unlearn the wrong way.

推荐答案

以下是您如何执行此操作的示例:

Here is an example of how you would do it:

在这里，我正在创建两个方法来包装两个操作，对于其他操作，您需要执行相同的操作:

Here I am creating two methods to wrap two operations, you need to do the same for the other operations:

public async Task<int> MergeOneDataTableAsync()
{
    // Merge One procedure
    using (SqlCommand cmd = new SqlCommand("MergeOneProcedure", dbc))
    {
        // 5 minute timeout on the query
        cmd.CommandTimeout = 300;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@TVP", MergeOneDataTable);

        return await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
    }
}


public async Task<int> MergeTwoDataTableAsync()
{
    // Merge Two procedure
    using (SqlCommand cmd = new SqlCommand("MergeTwoProcedure", dbc))
    {
        // 5 minute timeout on the query
        cmd.CommandTimeout = 300;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@TVP", MergeTwoDataTable);

        return await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
    }
}

请注意，我正在使用ExecuteNonQueryAsync方法执行查询.

Notice that I am using the ExecuteNonQueryAsync method to execute the query.

然后您的原始方法将如下所示:

And then your original method would look like this:

using (var dbc = new SqlConnection(db.Database.Connection.ConnectionString))
{
    dbc.Open();

    Task<int> task1 = MergeOneDataTableAsync();
    Task<int> task2 = MergeTwoDataTableAsync();

    Task.WaitAll(new Task[]{task1,task2}); //synchronously wait

    recordedStatistics.Add("mergeOne", task1.Result);
    recordedStatistics.Add("mergeTwo", task2.Result);
}

请注意，我正在使此方法保持同步.另一种选择(实际上是更好的选择)是将方法转换为异步方法，如下所示:

Please note that I am keeping this method synchronous. Another option (actually a better one) is to convert the method into an asynchronous one like this:

public async Task<Dictionary<string, int>> MyOriginalMethod()
{
    //...
    using (var dbc = new SqlConnection(db.Database.Connection.ConnectionString))
    {
        dbc.Open();

        Task<int> task1 = MergeOneDataTableAsync();
        Task<int> task2 = MergeTwoDataTableAsync();

        int[] results = await Task.WhenAll(new Task<int>[]{task1,task2});

        recordedStatistics.Add("mergeOne", results[0]);
        recordedStatistics.Add("mergeTwo", results[1]);
    }

    //...
    return recordedStatistics;
}

但这意味着您必须异步调用它(一路异步).

But this would mean that you have to invoke it asynchronously (async all the way).

这篇关于如何正确进行异步/并行数据库调用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！