提高数据上传到数据库的性能

时间：2022-02-08 16:53:24

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.

我有一个c#应用程序。当它运行时，它会计算大约50,000到100,000个值。然后将这些值存储到一个sql server数据库中。下面的代码运行良好，但是将数据上传到数据库需要很长时间。我想知道我能做些什么来提高性能?目前需要一分钟。

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?

我的Sql表如下所示。我是否应该在这里使用主键，因为我认为插入数据时必须花费额外的处理时间?

 tblResultEquityCurve

 DTime (smalldatetime) - primary key
 Equity numeric(18,4)

C# code

c#代码

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

1 个解决方案

#1

5  

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.

从循环调用DB存储过程不是一种好的实践。要么按照Jamez的建议使用SqlBulkCopy，要么创建另一个存储过程，通过用户定义的表类型接受表值参数，请参见这里。在MSSQL中，用户定义的类型有已知的可维维性问题，所以您可以使用XML，尽管它确实使您的sproc契约和签名不那么具有声明性。无论哪种方式，目标都是将一个异步调用中的所有数据传递给数据库。

#1

5  

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.

从循环调用DB存储过程不是一种好的实践。要么按照Jamez的建议使用SqlBulkCopy，要么创建另一个存储过程，通过用户定义的表类型接受表值参数，请参见这里。在MSSQL中，用户定义的类型有已知的可维维性问题，所以您可以使用XML，尽管它确实使您的sproc契约和签名不那么具有声明性。无论哪种方式，目标都是将一个异步调用中的所有数据传递给数据库。

标签：

相关文章