Run Two Queries in Parallel

Before I leave the topic of Parallel Queries, and Connections using Multiple Result Sets, I want to share a couple things about the syntax that make this worthwhile. Just because something is written to use asynchronous methods doesn’t mean it runs concurrently. That is a big point. If you truly have two queries that are completely non-dependent, and you wish them to be run at the same time, you have to write your code differently. With the parallel libraries, this is much easier to implement.

If you were to perform concurrent queries without using the parallel library, you would need to spawn a new thread for at least one of them, and then negotiate the processing of the individual threads such that your process waits for both queries to complete before moving forward. The parallel library allows you to do this same process without having to know all of the plumbing to make it work. Instead you need to know how to define your parallel Tasks, one for each query, and Await so that it waits for all Tasks to complete.

I created a .NetFiddle to demonstrate the difference between running two queries asynchronously, one running the queries in parallel, and the other running the queries serially.

I am running two queries. The first one returns a count of the business entities in the AdventureWorks2012.HumanResources.EmployeePayHistory table having more than one pay record. The second query returns a count from the same table of all business entities having only a single pay record. There are other, better ways to perform this, but it does provide two queries for our exercise.

In the example program you can find in the .Net Fiddle, I create two asynchronous methods to exercise each query. Both methods share the same connection, so the connection must allow Multiple Active Result Sets. I then provide two methods for calling these queries. The first opens a connection, and then runs the queries one after the other. The Second opens a connection and calls the queries in parallel. The only difference visible to you is the syntax.

Following is the method I wrote to run the two queries Sequentially

public static async Task RunSerialAsync()
{
    string connectionString = GetConnectionString();
    using (var awConnection =
        new SqlConnection(connectionString))
    {
        await awConnection.OpenAsync();
        var multiple = await GetEntitiesWithMultipleRatesAsync(awConnection);
        var single = await GetEntitiesWithSingleRateAsync(awConnection);
        Console.WriteLine("Multiple Price Entity Count: {0}", multiple);
        Console.WriteLine("Single Price Entity Count: {0}", single);
        awConnection.Close();
    }
}

In constrast, the following method runs the two queries Simultaneously

public static async Task RunParallelAsync()
{
    string connectionString = GetConnectionString();
    using (var awConnection =
        new SqlConnection(connectionString))
    {
        await awConnection.OpenAsync();
        var multiple = GetEntitiesWithMultipleRatesAsync(awConnection);
        var single = GetEntitiesWithSingleRateAsync(awConnection);
        await Task.WhenAll(multiple, single);
        Console.WriteLine("Multiple Price Entity Count: {0}", multiple.Result);
        Console.WriteLine("Single Price Entity Count: {0}", single.Result);
        awConnection.Close();
    }
}

The queries run really fast for me, so I added a three second delay in both. Running the parallel method, the process completes in just over 3 seconds. Running them sequentially, the process completes in just over 6 seconds. So, when you can benefit from running in parallel, you can take advantage of the capability.

Here are the results I get running on my laptop, having a Pentium Core 7.

Parallel Execution Results

Multiple Price Entity Count: 13
Single Price Entity Count: 277
Start: 09:09:41:677
Stop: 09:09:44:952
Duration (ms): 3275.0066

Sequential Execution Results

Multiple Price Entity Count: 13
Single Price Entity Count: 277
Start: 09:09:44:048
Stop: 09:09:50:199
Duration (ms): 6151.0103

You may notice that there is more time executing the queries themselves when they are run in parallel, 275 ms for parallel vs. 199 ms for running sequentially. However, the total execution time is greater when run sequentially. So, parallel does have a cost you need of which you should be aware.

That’s probably enough on parallel for quite some time. I hope this helps.

Cheers,

Ben