I have a c# application. When it runs it calculates about 50,000 to 100,000 values. I then store these values into a sql server database. The code below works fine however it takes a very long time to upload the data to the database. I would like to know if there is anything I can do to improve the performance? It currently take over a minute.
My Sql table shown below. Should I be using a primary key here as I imagine this must take extra processing time when inserting the data?
tblResultEquityCurve
DTime (smalldatetime) - primary key
Equity numeric(18,4)
C# code
void exEquityCurveMT()
{
DeletePreviousResultsFromTable("Result_EquityCurve");
Spliter[] split = MTSplitter(Account.EquityHistory.Count);
MultiThreadToDataBase[] mtDB = new MultiThreadToDataBase[NUMCORES];
Task[] taskDB = new Task[NUMCORES];
for (int i = 0; i < taskDB.Length; i++)
{
List<structEquity> eqyList = Account.EquityHistory.GetRange((int)split[i].rowStart, split[i].numRows);
mtDB[i] = new MultiThreadToDataBase();
taskDB[i] = Task.Factory.StartNew(mtDB[i].exEquityCurve, eqyList);
}
try
{
Task.WaitAll(taskDB);
}
catch (AggregateException ex)
{
ExceptionDispatchInfo.Capture(ex.InnerException).Throw();
}
}
public void exEquityCurve(object dataObj)
{
List<structEquity> dataList = (List<structEquity>)dataObj;
using (connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlCommand commandEquity = new SqlCommand("dbo.InsertEquityCurve", connection))
{
commandEquity.CommandType = System.Data.CommandType.StoredProcedure;
commandEquity.Parameters.Add("@dtTime", System.Data.SqlDbType.SmallDateTime);
commandEquity.Parameters.Add("@Equity", System.Data.SqlDbType.Float);
for (int i = 0; i < dataList.Count; i++)
{
commandEquity.Parameters["@dtTime"].Value = dataList[i].dTime;
commandEquity.Parameters["@Equity"].Value = dataList[i].Equity;
commandEquity.ExecuteNonQuery();
}
}
connection.Close();
}
}