C# DataTable數據遍歷優化詳解
我們在進行開發時,會經常使用DataTable來存儲和操作數據,我發現在遍歷DataTable並對數據進行刪除和添加操作時速度非常慢,查閱相關資料並測試在添加主鍵後可以使遍歷和操作速度提高很多:
測試代碼,測試的是我們向取出來數據滿足Flag!=1條件的所有數據的後面添加一條數據(因為這條數據的一些字段值是根據前面的幾條滿足條件[“AccID=’” + accID + “’ AND Y=’” + year + “’ AND AbsID <= ” + absID;]數據的值累加得到的)所以需要進行整個DataTable的遍歷來計算添加:
public static void Test2() { Stopwatch watch = new Stopwatch(); using (DbConnection conn = SqlHelper.GetConnection("ConnectionString")) { using (SqlCommand cmd = new SqlCommand()) { watch.Start(); cmd.CommandText = string.Format(@" select ROW_NUMBER() OVER (Order by S.AccID,S.CurrID,S.AbsID,S.Flag)AS RowNum,S.* from Test S "); cmd.Connection = conn as SqlConnection; cmd.CommandTimeout = 60000; conn.Open(); DataTable table = ExecuteDataTable(cmd); watch.Stop(); Console.WriteLine("從數據庫取出數據{0}條", table.Rows.Count); Stopwatch watch2 = new Stopwatch(); watch2.Start(); DataTable newTable = HandleAccYear(table,true); watch2.Stop(); Console.WriteLine("數據{0},遍歷操作時間:毫秒:{1},秒:{2}", newTable.Rows.Count, watch2.ElapsedMilliseconds, watch2.ElapsedMilliseconds / 1000); } conn.Close(); } }
填充數據到DataTable的方法
public static DataTable ExecuteDataTable(SqlCommand cmd) { DataTable table = new DataTable(); SqlDataAdapter adaper = new SqlDataAdapter(cmd); adaper.Fill(table); return table; }
private static DataTable HandleAccYear(DataTable dt, bool isCurrency) { DataTable newdt = dt.Clone(); //不使用主鍵 //dt.PrimaryKey = new DataColumn[] { // dt.Columns["AccID"], // dt.Columns["Flag"], // dt.Columns["AbsID"], // dt.Columns["RowNum"], //}; if (dt.Rows.Count > 0) { object flag = null; foreach (DataRow row in dt.Rows) { flag = row["Flag"]; if (flag != null && !Helper.AreEqual(flag.ToString(), "1")) { DataRow newRow = newdt.NewRow(); DataRow sourceRow = newdt.NewRow(); sourceRow.ItemArray = row.ItemArray; newRow.ItemArray = row.ItemArray; string accID = row["AccID"].ToString(), year = row["Y"].ToString(), absID = row["AbsID"].ToString(); newRow["Flag"] = "5"; newRow["SumInfo"] = "測試數據"; string filter = "AccID='" + accID + "' AND Y='" + year + "' AND AbsID <= " + absID; if (!isCurrency) { filter = "AccID='" + accID + "'AND CurrID='" + row["CurrID"] + "' AND Y='" + year + "' AND AbsID <= " + absID; } DataRow[] selectRow = dt.Select(filter); double debitLC = 0, debitQty = 0, creditLC = 0, creditQty = 0, debitFC = 0, creditFC = 0; foreach (DataRow item in selectRow) { debitLC += ToDouble(item["YearDebitLC"]); debitQty += ToDouble(item["YearDebitQty"]); creditLC +=ToDouble(item["YearCreditLC"]); creditQty += ToDouble(item["YearCreditQty"]); if (!isCurrency) { debitFC += ToDouble(item["YearDebitFC"]); creditFC += ToDouble(item["YearCreditFC"]); } } newRow["CurDebitLC"] = debitLC; newRow["CurDebitQty"] = debitQty; newRow["CurCreditLC"] = creditLC; newRow["CurCreditQty"] = creditQty; //newRow["CurDebitLC"] = dt.Compute("Sum(YearDebitLC)", filter); //newRow["CurDebitQty"] = dt.Compute("Sum(YearDebitQty)", filter); //newRow["CurCreditLC"] = dt.Compute("Sum(YearCreditLC)", filter); //newRow["CurCreditQty"] = dt.Compute("Sum(YearCreditQty)", filter); if (!isCurrency) { //newRow["CurCreditFC"] = dt.Compute("Sum(YearCreditFC)", filter); //newRow["CurDebitFC"] = dt.Compute("Sum(YearDebitFC)", filter); newRow["CurCreditFC"] = creditFC; newRow["CurDebitFC"] = debitFC; } newdt.Rows.Add(sourceRow); newdt.Rows.Add(newRow); } else { DataRow sourceRow = newdt.NewRow(); sourceRow.ItemArray = row.ItemArray; newdt.Rows.Add(sourceRow); } } } return newdt; }
當不使用主鍵進行遍歷計算插入相應的值時所用時間竟然是這麼多:
當我使用同樣的方法,同樣的數據添加主鍵(即把HandleAccYear方法中不使用主鍵下面的註釋去掉後).進行遍歷計算等操作,得出的結果竟然有這麼大的差別:
補充:C# DataTable數據量大,循環處理數據的時候優化速度
相信大傢用for循環datatable數據的不會太少,這個在數據量比較小的時候可以接受,但是數據量大的時候卻會造成CPU占用過高,甚至把電腦資源耗盡卡死至無限等待,
其實一些循環耗時的操作可以用線程池分塊來處理,這樣會減輕CPU很多壓力,好比食堂打飯,當隻有一個窗口的時候勢必等待的時間會非常的長,但是多開幾個窗口的時候卻大大提高效率,
C#中用線程池就可以做到,本來一開始的時候我用的是為每個區塊開一個線程,但是有一個問題就是開瞭那麼多的線程沒辦法結束他們,後來我想到瞭線程池,
具體代碼如下:
int sid = dt.Rows.Count % 100 == 0 ? (dt.Rows.Count / 100) : (dt.Rows.Count / 100 + 1); for (int a = 1; a <= sid; a++) { object aa=a.ToString() + "," + sid.ToString(); ThreadPool.QueueUserWorkItem(todo , aa); } public void todo(object aa) { string sql = ""; int startindex = Convert.ToInt32(aa.ToString().Split(',')[0]); int limitstep = Convert.ToInt32(aa.ToString().Split(',')[1]); for (int i = (startindex > 1 ? ((startindex - 1) * 100) : 0); i < (startindex == limitstep ? (dt.Rows.Count) : startindex*100); i++) { //todo數據操作 } Thread.Sleep(2000); }
以上為個人經驗,希望能給大傢一個參考,也希望大傢多多支持WalkonNet。如有錯誤或未考慮完全的地方,望不吝賜教。
推薦閱讀:
- C# Datatable的幾種用法小結
- C#獲取DataTable對象狀態DataRowState
- SQLServer批量插入數據的三種方式及性能對比
- C#中DataTable的創建與遍歷實現
- C#中DataSet,DataTable,DataView的區別與用法