关于代码性能优化的总结 - 程序员不帅哥
source link: https://www.cnblogs.com/Mr-Worlf/p/18020445
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
关于代码性能优化的总结
今天同事发开中遇到了一个代码性能优化的问题,原本需求是:从一个数据库中查询某个表数据,存放到datatable中,然后遍历datatable,看这些数据在另一个数据库的表中是否存在,存在的话就要更新,不存在就要插入。
就这个需求本身来说很简单,但是随着数据量的增大,之前通过循环遍历的方式就出现了性能问题。我在思索片刻后,给出的建议是分页查询和利用事务批量提交。
1.利用数据库事务批量提交
1 using (SqlTransaction transaction = targetConnection.BeginTransaction()) 2 { 3 foreach (DataRow row in dataTable.Rows) 4 { 5 if (CheckIfDataExists(targetConnection, transaction, row)) 6 { 7 UpdateData(targetConnection, transaction, row); 8 } 9 else 10 { 11 InsertData(targetConnection, transaction, row); 12 } 13 } 14 15 transaction.Commit(); 16 } 17 } 18 19 //下面两个方法都还可以优化,需要接收批量sql语句,所以可以修改成list<SqlCommand>,然后遍历执行,此处能说明问题即可 20 private void UpdateData(SqlConnection connection, SqlTransaction transaction, DataRow row) 21 { 22 using (SqlCommand command = new SqlCommand("UPDATE YourTable SET YourUpdateStatement WHERE YourCondition", connection, transaction)) 23 { 24 // Add parameters to your command here, based on your update statement and condition 25 // command.Parameters.AddWithValue("@ParameterName", row["ColumnName"]); 26 27 command.ExecuteNonQuery(); 28 } 29 } 30 31 private void InsertData(SqlConnection connection, SqlTransaction transaction, DataRow row) 32 { 33 using (SqlCommand command = new SqlCommand("INSERT INTO YourTable (YourColumns) VALUES (YourValues)", connection, transaction)) 34 { 35 // Add parameters to your command here, based on your columns and values 36 // command.Parameters.AddWithValue("@ParameterName", row["ColumnName"]); 37 38 command.ExecuteNonQuery(); 39 } 40 }
看到这里的时候,大家可以考虑下,以上方案还有什么优化的地方吗?
当然是有的,如果数据量持续增大,datatable这样直接加载到内存的方式恐怕会成为性能问题点吧,我们得考虑怎么优化才能避免将大数据一次性加载到内存,大部分同学第一个想到的就是分页,这个方案当然是没有错,但是还不够高级,给大家提示一个关键字“yield”,或许从聪明的你已经悟到了,接着往下看。
2.流式处理法
什么是流式处理法呢?
1 private IEnumerable<DataRow> GetDataFromSource() 2 { 3 using (SqlConnection sourceConnection = new SqlConnection(sourceConnectionString)) 4 { 5 sourceConnection.Open(); 6 7 using (SqlCommand command = new SqlCommand("SELECT * FROM YourTable", sourceConnection)) 8 { 9 using (SqlDataReader reader = command.ExecuteReader()) 10 { 11 DataTable dataTable = new DataTable(); 12 13 while (reader.Read()) 14 { 15 dataTable.LoadDataRow(reader.GetValues(), LoadOption.Upsert); 16 DataRow row = dataTable.Rows[dataTable.Rows.Count - 1]; 17 yield return row; 18 dataTable.Clear(); 19 } 20 } 21 } 22 }
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK