I have a program that makes use of SQL Server
to pull information from a database and then perform a series of insertions into other tables and also send an email with the data that was retireved.
The program takes around 3 and a half minutes to execute and there is only 5 rows of data in the database. I am trying to reduce this time in any way I can and have tried multithreading
which seems to slow it down further, and TPL
which neither increases nor reduces the time. Does anyone know why I am not seeing performance improvements?
I am using an Intel Core i5
which I know has 2 cores so using more than 2 cores I understand will reduce performance. Here is how I am incorporating the use of tasks:
private static void Main(string[] args)
{
Util util = new Util(); //Util object
List<Data> dataList = new List<Data>(); //List of Data Objects
//Reads each row of data and creates Data obj for each
//Then adds each object to the list
dataList = util.getData();
var stopwatch = Stopwatch.StartNew();
var tasks = new Task[dataList.Count];
int i = 0; //Count
foreach (Data data in dataList)
{
//Perform insertions and send email with data
tasks[i++] = Task.Factory.StartNew(() => util.processData(data));
}
Task.WaitAll(tasks); //Wait for completion
Console.WriteLine("DONE: {0}", stopwatch.ElapsedMilliseconds);
}
Util Class:
class Util
{
// create and open a connection object
SqlConnection conn = new SqlConnection("**Connection String**");
//Gets all results from table, and adds object to list
public List<Data> getData()
{
conn.Open();
SqlCommand cmd = new SqlCommand("REF.GET_DATA", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataReader reader = cmd.ExecuteReader();
List<Data> dataList = new List<Data>();
while (reader.Read())
{
//** Take data from table and assigns them to variables
//** Removed for simplicity
Data data= new Data(** pass varaibles here **);
dataList.Add(data); //Add object to datalist
}
return dataList;
}
public void processData(Data data)
{
//** Perform range of trivial operations on data
//** Removed for simplicity
byte[] results = data.RenderData(); //THIS IS WHAT TAKES A LONG TIME TO COMPLETE
data.EmailFile(results);
return;
} //END handleReport()
}
Am I using tasks in the wrong place? Should I instead be making use of parellelism in the util.processData()
method? I also tried using await
and async
around the util.processData(data)
call in the main method with no improvements.
EDIT:
Here is the renderData
function:
//returns byte data of report results which will be attatched to email.
public byte[] RenderData(string format, string mimeType, ReportExecution.ParameterValue[] parameters)
{
ReportExecutionService res = new ReportExecutionService();
res.Credentials = System.Net.CredentialCache.DefaultCredentials;
res.Timeout = 600000;
//Prepare Render arguments
string historyID = null;
string deviceInfo = String.Empty;
string extension = String.Empty;
string encoding = String.Empty;
ReportExecution.Warning[] warnings = null;
string[] streamIDs = null;
byte[] results = null;
try
{
res.LoadReport(reportPath, historyID);
res.SetExecutionParameters(parameters, "en-gb"); //"/LSG Reporting/Repossession Sales (SAL)/SAL004 - Conveyancing Matter Listing"
results = res.Render(format, deviceInfo, out extension, out mimeType, out encoding, out warnings, out streamIDs);
break;
}
catch (Exception ex)
{
Console.WriteLine(ex.StackTrace)
}
return results;
}