task parallel library

[vinaychandrashekhar]

I am having an issue when using Task Parallel Library of .NET 4.0 along with ADO.Net.I am trying to execute two stored procedures parallel by creating two seperate tasks for each of them. The scenario is something like this.First stored procedure takes 20 sec to execute and the other takes around 12 sec.When I run the code,the time taken for execution is summation of these two time periods (i.e 20 + 12 = 32 sec).This is not what I am expecting.What I am assuming is that by the time first stored procedure has finished executing, the second one would have already finished and hence the total time taken for execution should be somewhere around 20 sec.I have also observed, that in my code,if i replace the ADO.Net section with just

Thread.Sleep(time), the result i get is expected i.e, if I set two threads of 20 and 12 sec, the program executes within 20 sec.This is the same behaviour I am trying to acheive using ADO.Net. I have also observered that, if I have these statements,

IAsyncResult result1 = cmd1.BeginExecuteReader();
IDataReader reader1 = cmd1.EndExecuteReader(result1);

for one of the stored procedure, I get expected behaviour.However, when I apply the same to the other stored procedure result,the program executes sequentially. I am confused about this behaviour. Hence, request any of you to help me in achieving the result I am looking for. I am attaching full code here.Please let me know where am I going wrong.

Code:

string connectionString1 = ConfigurationManager.ConnectionStrings["ConnectionString1"].ConnectionString;
string connectionString2 = ConfigurationManager.ConnectionStrings["ConnectionString2"].ConnectionString;
string connectionString3 = ConfigurationManager.ConnectionStrings["ConnectionString3"].ConnectionString;
List<int> results = new List<int>();
Dictionary<string, int> dict = new Dictionary<string, int>();

List<SqlDataReader> rdrs = new List<SqlDataReader>();

public List<int> returnSelect()
{

var taskIndex = -1;
var start = DateTime.Now;
Task[] tasks = new Task[]
{
Task.Factory.StartNew(() => PrintNumber1()),
Task.Factory.StartNew(() => PrintNumber2()),
Task.Factory.StartNew(() => PrintNumber3())
};
Dictionary<int, DateTime> taskList = new Dictionary<int, DateTime>();
while (tasks.Length > 0)
{
taskIndex = Task.WaitAny(tasks);
for (int i = 0; i < tasks.Count(); i++)
{
Console.Write("Array {0} | ", tasks[i].Id);

}
taskList.Add(tasks[taskIndex].Id, DateTime.Now);
tasks = tasks.Where((t) => t != tasks[taskIndex]).ToArray();
}
var end = DateTime.Now;
var diff = end - start;
int count = rdrs.Count;
int dcount = dict.Count;
return results;

}

//19
public void PrintNumber1()
{
SqlConnection con1 = new SqlConnection(connectionString1);
{
var start = DateTime.Now;
var copen = DateTime.Now;

var cclose = DateTime.Now;
int exSec = (cclose - copen).Seconds;
SqlCommand cmd1 = new SqlCommand("PS_Brand_level", con1);
cmd1.CommandType = CommandType.StoredProcedure;
cmd1.Parameters.AddWithValue("@startdate", "6/25/2012");
cmd1.Parameters.AddWithValue("@enddate", "11/18/2012");
cmd1.Parameters.AddWithValue("@subbrands", "mybrands");
cmd1.Parameters.AddWithValue("@count", 11);
cmd1.Parameters.AddWithValue("@category", "a");
con1.Open();
IAsyncResult result1 = cmd1.BeginExecuteReader();
IDataReader reader1 = cmd1.EndExecuteReader(result1);
var end = DateTime.Now;
dict.Add((end - start).ToString(), 1);
}

/*var start = DateTime.Now;
Thread.Sleep(19000);
var end = DateTime.Now;
dict.Add((end - start).ToString(), 1);*/
}

//9
public void PrintNumber2()
{

SqlConnection con2 = new SqlConnection(connectionString2);
{
var start = DateTime.Now;
var copen = DateTime.Now;
con2.Open();
var cclose = DateTime.Now;
int exSec = (cclose - copen).Seconds;
DataSet dtPSPercentage = new DataSet();
SqlCommand cmd2 = new SqlCommand("Ps_Percentage", con2);
cmd2.CommandType = CommandType.StoredProcedure;
cmd2.CommandTimeout = 100;
cmd2.Parameters.AddWithValue("@startdate", "6/25/2012");
cmd2.Parameters.AddWithValue("@enddate", "11/18/2012");
cmd2.Parameters.AddWithValue("@subbrands", "mybrands");
cmd2.Parameters.AddWithValue("@category", "a");

IAsyncResult result2 = cmd2.BeginExecuteReader();
IDataReader reader2 = cmd2.EndExecuteReader(result2);
var end = DateTime.Now;
dict.Add((end - start).ToString(), 2);
}

/*var start = DateTime.Now;
Thread.Sleep(9000);
var end = DateTime.Now;
dict.Add((end - start).ToString(), 2);*/
}

//12
public void PrintNumber3()
{

SqlConnection con3 = new SqlConnection(connectionString3);
{
var start = DateTime.Now;
var copen = DateTime.Now;
//con3.Open();
var cclose = DateTime.Now;
int exSec = (cclose - copen).Seconds;
SqlCommand cmd1 = new SqlCommand("Ps_Aggregated", con3);
cmd1.CommandType = CommandType.StoredProcedure;
cmd1.Parameters.AddWithValue("@startdate", "6/25/2012");
cmd1.Parameters.AddWithValue("@enddate", "11/18/2012");
cmd1.Parameters.AddWithValue("@subbrands", "mybrands");
cmd1.Parameters.AddWithValue("@category", "a");

// IAsyncResult result1 = cmd1.BeginExecuteReader(CommandBehavior.SingleRow);
//SqlDataReader reader1 = cmd1.EndExecuteReader(result1);
var end = DateTime.Now;
dict.Add((end - start).ToString(), 3);

}

/*var start = DateTime.Now;
Thread.Sleep(12000);
var end = DateTime.Now;
dict.Add((end - start).ToString(), 3);*/

}
Thanks & Regards