我们在进行开发时,会经常使用DataTable来存储和操作数据,我发现在遍历DataTable并对数据进行删除和添加操作时速度非常慢,查阅相关资料并测试在添加主键后可以使遍历和操作速度提高很多:
测试代码,测试的是我们向取出来数据满足Flag!=1条件的所有数据的后面添加一条数据(因为这条数据的一些字段值是根据前面的几条满足条件[“AccID='” + accID + “' AND Y='” + year + “' AND AbsID <= ” + absID;]数据的值累加得到的)所以需要进行整个DataTable的遍历来计算添加:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
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的方法
1
2
3
4
5
6
7
|
public static DataTable ExecuteDataTable(SqlCommand cmd) { DataTable table = new DataTable(); SqlDataAdapter adaper = new SqlDataAdapter(cmd); adaper.Fill(table); return table; } |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
|
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#中用线程池就可以做到,本来一开始的时候我用的是为每个区块开一个线程,但是有一个问题就是开了那么多的线程没办法结束他们,后来我想到了线程池,
具体代码如下:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
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); } |
以上为个人经验,希望能给大家一个参考,也希望大家多多支持服务器之家。如有错误或未考虑完全的地方,望不吝赐教。
原文链接:https://blog.csdn.net/lovestj/article/details/80481278