Execute multiple queries in parallel with Dapper

Question

I trying to execute three similar SQL queries in parallel using Dapper and stored procedures to get three similar results after all queries are completed.

Here is my code:

public class SomeReport
{
    private static readonly string ConnectionString = ConfigurationManager.ConnectionStrings["SomeContext"].ToString();
    public ReportStatus ReportStatus { get; set; }
    public long NetworkServerTime { get; set; }
    public string ReportLastErrorMessage { get; set; }

    public RowSet[] FirstRowSet { get; set; }
    public RowSet[] SecondRowSet { get; set; }
    public RowSet[] ThirdRowSet { get; set; }

    public Report()
    {
        NetworkServerTime = 0;
        ReportStatus = ReportStatus.NotCreated;
    }


    public async Task GetReportDataAsync(ReportParameters parameters)
    {
        DynamicParameters requestParameters = new DynamicParameters();
        requestParameters.Add("@sinceDateFilter", parameters.SinceDate?.Date, DbType.DateTime);
        requestParameters.Add("@untilDateFilter", parameters.UntilDate?.Date, DbType.DateTime);
        requestParameters.Add("@countryId", parameters.CountryId, DbType.Int32);

        ReportLastErrorMessage = null;

        Task allTasks = null;
        var stopWatch = new Stopwatch();

        try
        {
            var firstTask = GetRows("[dbo].[GET_Report_FirstRowSet]", requestParameters);
            var secondTask =
                GetRows("[dbo].[GET_Report_SecondRowSet]", requestParameters);
            var thirdTask =
                GetRows("[dbo].[GET_Report_ThirdRowSet]", requestParameters);

            allTasks = Task.WhenAll(firstTask, secondTask, thirdTask);

            FirstRowSet = await firstTask;
            SecondRowSet = await secondTask;
            ThirdRowSet = await thirdTask;
        }
        catch (Exception ex)
        {
            ReportStatus = ReportStatus.Error;
            ReportLastErrorMessage = allTasks?.Exception?.InnerExceptions.Last().Message;
        }
        finally
        {
            if (ReportStatus != ReportStatus.Error)
            {
                ReportStatus = ReportStatus.Success;
                NetworkServerTime = stopWatch.ElapsedMilliseconds;
            }

        }
        stopWatch.Reset();
    }

    private async Task<RowSet[]> GetRows(string procName, DynamicParameters parameters)
    {
        using (var conn = new SqlConnection(ConnectionString))
        {
            RowSet[] rowsSet;

            try
            {
                var sqlString = string.Concat(procName, " @sinceDateFilter, @untilDateFilter, @countryId");
                var query = await conn.QueryAsync<RowSet>(sqlString, parameters, commandTimeout: 500);
                rowsSet = query.ToArray();
            }
            catch (SqlException sqlEx)
            {
                rowsSet = new RowSet[0];
                throw;
            }

            return rowsSet;
        }
    }
}

But when I launch the debugger and SQL Server Profiler, I see that the queries are executed sequentially when creating the tasks corresponding to them.

How can I make queries start running at the same time and run in parallel?

Answer 1

if I use debugger and sql-profiler, I see that the first query in the profiler is executed when I am on the line of code var firstTask = GetRows("[dbo].[GET_Report_FirstRowSet]", requestParameters); but not when i am on the line of code allTasks = Task.WhenAll (firstTask, secondTask, thirdTask);

This is correct and normal. The way async/await works is that control is returned up the call-stack as soon as the first incomplete await happens, which in your case is the await conn.QueryAsync<RowSet>. However, you've still started the ball rolling simply by calling the async method. The operation isn't held at some pending state waiting for you to call Task.WhenAll, so we expect it to have already begun. Task.WhenAll doesn't do anything except aggregate the await step - it has no role in making things actually happen.

So: I suspect that everything is already working as expected, but simply: the tasks are reporting as starting in the order you have requested. Which is... exactly what we expect.