CODEWITHSUNDEEP
c#sql.netsql-server
mHelpMe
mHelpMe

Reputation: 6668

improve performance of uploading data to database

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();
            }
        }

Upvotes: 3

Views: 139

Answers (1)

ironstone13
ironstone13

Reputation: 3443

It is not a good practice to call a DB stored procedure from a loop. Either use SqlBulkCopy as suggested by Jamez, or create another stored procedure that would accept a table valued parameter via user defined table type, see here. User defined types in MSSQL have known maintainability issues, so you could use XML instead, although it does make you sproc contract and signature less declarative. Either way the goal is to pass all data in one async call to the database.

Upvotes: 5

Related Questions