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?
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 codeallTasks = 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.