datatable.select 性能测试

20元现金领取地址：http://jdb.jiudingcapital.com/phone.html
内部邀请码：C8E245J （不写邀请码，没有现金送）

国内私募机构九鼎控股打造，九鼎投资是在全国股份转让系统挂牌的公众公司，股票代码为430719，为“中国PE第一股”，市值超1000亿元。

由于刚好看到网上的一篇帖子，所以做了这个测试。测试结果发现：
1、DataView.RowsFilter 比 DataTable.Select 性能低很多。查询了MSDN ，发现 DataView 重要的一个功能就是 “筛选”数据，为什么性能还不如 DataTable.Select() 呢？
2、当做 1000w 数据测试时，出现了 SystemOutOfMemory 的异常。主要在这句：
   // 1000w 数据时，出现了 SystemOutOfMemory 的异常
   row = mDataTable.NewRow();
   问题是：难道使用 NewRow() 时，有上限限制？
3、1000w 数据测试时，出现一个现象：添加前300w 数据时，时间花费都差不多；300-400w 时，时间是前面的一倍；400-500w 时间更是长久；好像越到后来时间越长？为什么呢？
   1000w 数据测试“循环添加数据”功能的结果：
      Adding data ...
      Readed 1000000 rows [ 7651.0016 ms]...
      Readed 2000000 rows [ 8341.9952 ms]...
      Readed 3000000 rows [ 7030.1088 ms]...
      Readed 4000000 rows [ 24545.2944 ms]...
      Readed 5000000 rows [ 70251.016 ms]...
      Readed 6000000 rows [ 369591.4464 ms]...
      Readed 7000000 rows [ 668561.344 ms]...
      Readed 8000000 rows [ 1384751.1744 ms]...
      // 再往下就出现了 2 中所描述的异常
人老实，测试这么做也是没有办法，自己测试时，发现了好多需要改进的地方，希望大家不要见笑，帮我看看上面的问题，谢谢！

测试代码：

测试代码
1

class Program
2

{
3

const int ROWS_COUNT = 1; // 设置处理数据大小。单位：w 万
4

const int ROWS_COUNT_UNIT = 10000; // 数据行单位：万
5

const double ROWS_TOTAL_COUNT = ROWS_COUNT * ROWS_COUNT_UNIT; // 总循环次数
6

const double ROWS_SHOW_INFO_LINE = ROWS_TOTAL_COUNT / 10; // 显示进度
7

private static string SAVE_FILE_NAME = string.Format("{0}w_{1}", ROWS_COUNT.ToString(), DateTime.Now.ToString("yyyyMMddhhmmss"));
8

private static StringBuilder builder = new StringBuilder(string.Format("ROWS COUNT :{0}w\n", ROWS_COUNT));
9

private static DataTable mDataTable = null;
10

private static DataView dv = null;
11

private static string str = string.Empty;
12

static void Main(string[] args)
14

{
15

// 添加数据行
16

DateTime start = DateTime.Now;
17

AddData();
18

str = string.Format("cost [ {0} ms ] to add [ {1}w ] rows.\n{2}\n",
19

(DateTime.Now.Subtract(start).TotalMilliseconds.ToString()),
20

ROWS_COUNT.ToString(),
21

"----- AddData() END -----\n");
22

Console.WriteLine(str);
23

builder.AppendFormat(str);
24

// 添加 DataTable 的主键
26

start = DateTime.Now;
27

AddPrimaryKey(true);
28

str = string.Format("cost [ {0} ms ] to AddPrimaryKey().\n{1}\n",
29

(DateTime.Now.Subtract(start).TotalMilliseconds.ToString()),
30

"----- AddPrimaryKey() END -----\n");
31

Console.WriteLine(str);
32

builder.AppendFormat(str);
33

// 循环进行 10 次数据过滤测试
35

start = DateTime.Now;
36

int times = 0;
37

string enter = string.Empty;
38

while ((++times) < 11)
39

{
40

str = string.Format("running {0} times

\n", times.ToString());
41

Console.BackgroundColor = ConsoleColor.Red;
42

Console.WriteLine(str);
43

Console.ResetColor();
44

builder.AppendFormat(str);
45

Test();
46

builder.Append("\n\n");
47

}
48

str = string.Format("\ncost [ {0} ms ] to WHILE loop.\n",
49

(DateTime.Now.Subtract(start).TotalMilliseconds.ToString()));
50

Console.WriteLine(str);
51

builder.AppendFormat(str);
52

// 测试结果输入到文件中
54

WriteToFile(builder.ToString());
55

// 提示用户操作
57

Console.WriteLine("Press any key to continue

");
58

Console.ReadLine();
59

}
60

/// <summary>
62

/// 创建dataTable
63

/// </summary>
64

private static void AddData()
65

{
66

Console.WriteLine("Adding data

");
67

mDataTable = null;
69

mDataTable = new DataTable("test");
70

mDataTable.Columns.Add(new DataColumn("index", typeof(double)));
72

mDataTable.Columns.Add(new DataColumn("content", typeof(string)));
73

DateTime start = DateTime.Now;
75

TimeSpan end;
76

double rowsCount = ROWS_TOTAL_COUNT + 1;
78

DataRow row = null;
79

for (int i = 1; i < rowsCount; i++)
80

{
81

if (0 == (i % ROWS_SHOW_INFO_LINE))
82

{
83

end = DateTime.Now.Subtract(start);
84

str = string.Format("Readed {0} rows [ {1} ms]

\n\n", i.ToString(), end.TotalMilliseconds.ToString());
85

Console.WriteLine(str);
86

builder.AppendFormat(str);
87

start = DateTime.Now;
88

}
89

// 1000w 数据时，出现了 SystemOutOfMemory 的异常
90

row = mDataTable.NewRow();
91

row["index"] = i;
93

row["content"] = i.ToString();
94

mDataTable.Rows.Add(row);
96

}
97

mDataTable.AcceptChanges();
99

100

Console.Clear();
101

}
102

103

/// <summary>
104

/// 添加主键值
105

/// </summary>
106

/// <param name="add"></param>
107

private static void AddPrimaryKey(bool add)
108

{
109

Console.WriteLine("Adding Primarykey

");
110

DateTime start;
111

112

if (add)
113

{
114

// add PrimaryKey columns
115

int key = mDataTable.PrimaryKey.Length;
116

DataColumn[] dcs = new DataColumn[key + 2];
117

dcs[key + 0] = mDataTable.Columns["index"];
118

dcs[key + 1] = mDataTable.Columns["content"];
119

120

start = DateTime.Now;
121

mDataTable.PrimaryKey = dcs;
122

str = string.Format("cost [ {0} ms ] mDataTable.PrimaryKey

\n", (DateTime.Now.Subtract(start).TotalMilliseconds.ToString()));
123

Console.WriteLine(str);
124

builder.AppendFormat(str);
125

}
126

127

start = DateTime.Now;
128

dv = null;
129

dv = mDataTable.DefaultView;
130

str = string.Format("cost [ {0} ms ] mDataTable.DefaultView

\n", (DateTime.Now.Subtract(start).TotalMilliseconds.ToString()));
131

Console.WriteLine(str);
132

builder.AppendFormat(str);
133

134

builder.Append("PrimaryKey Columns Details;\n");
135

foreach (DataColumn dc in mDataTable.PrimaryKey)
136

{
137

builder.AppendFormat("Column Name: \t{0}\nColumn DataType: \t{1}\n", dc.ColumnName, dc.DataType.Name);
138

}
139

}
140

141

/// <summary>
142

/// 数据筛选测试
143

/// </summary>
144

private static void Test()
145

{
146

const string CONDITIONS_1 = "index < 80000";
147

const string CONDITIONS_2 = "index < 80000 or (content LIKE '%200%')";
148

149

SelectFromDatatable(CONDITIONS_1);
150

SelectFromDatatable(CONDITIONS_2);
151

152

FilterDataView(CONDITIONS_1);
153

FilterDataView(CONDITIONS_2);
154

}
155

156

/// <summary>
157

/// 将结果记录到文件
158

/// </summary>
159

/// <param name="content"><文件内容/param>
160

private static void WriteToFile(string content)
161

{
162

try
163

{
164

System.IO.StreamWriter sw = new System.IO.StreamWriter(string.Format("{0}.txt", SAVE_FILE_NAME), false, Encoding.UTF8);
165

sw.Write(content);
166

sw.Close();
167

sw = null;
168

}
169

catch (Exception exp)
170

{
171

System.Diagnostics.Debug.WriteLine(exp.Message);
172

}
173

}
174

175

/// <summary>
176

/// 通过DataTabl.Select 过滤数据
177

/// </summary>
178

/// <param name="condtions"></param>
179

private static void SelectFromDatatable(string condtions)
180

{
181

DateTime start = DateTime.Now;
182

start = DateTime.Now;
183

mDataTable.Select(condtions);
184

builder.AppendFormat("mDataTable.Select(\"{0}\"):\n\t{1} ms\n", condtions, (DateTime.Now.Subtract(start).TotalMilliseconds.ToString()));
185

}
186

187

/// <summary>
188

/// 通过 DataTable.DefaultView.RowFilter 过滤数据
189

/// </summary>
190

/// <param name="conditions"></param>
191

private static void FilterDataView(string conditions)
192

{
193

DateTime start = DateTime.Now;
194

start = DateTime.Now;
195

dv.RowFilter = conditions;
196

builder.AppendFormat("dv.RowFilter = \"{0}\": \n\t{1} ms\n", conditions, (DateTime.Now.Subtract(start).TotalMilliseconds.ToString()));
197

}
198

}
199

200

测试结果：

ROWS COUNT :50w
Readed 50000 rows [ 230.3312 ms]...
Readed 100000 rows [ 240.3456 ms]...
Readed 150000 rows [ 390.5616 ms]...
Readed 200000 rows [ 220.3168 ms]...
Readed 250000 rows [ 250.36 ms]...
Readed 300000 rows [ 450.648 ms]...
Readed 350000 rows [ 220.3168 ms]...
Readed 400000 rows [ 240.3456 ms]...
Readed 450000 rows [ 340.4896 ms]...
Readed 500000 rows [ 220.3168 ms]...
cost [ 3034.3632 ms ] to add [ 50w ] rows.
----- AddData() END -----

cost [ 8321.9664 ms ] mDataTable.PrimaryKey...
cost [ 26658.3328 ms ] mDataTable.DefaultView...
PrimaryKey Columns Details;
Column Name: index
Column DataType: Double
Column Name: content
Column DataType: String
cost [ 34980.2992 ms ] to AddPrimaryKey().
----- AddPrimaryKey() END -----

running 1 times ...
mDataTable.Select("index < 80000"):
440.6336 ms
mDataTable.Select("index < 80000 or (content LIKE '%200%')"):
3304.752 ms
dv.RowFilter = "index < 80000":
27880.0896 ms
dv.RowFilter = "index < 80000 or (content LIKE '%200%')":
28591.112 ms

running 2 times ...
mDataTable.Select("index < 80000"):
430.6192 ms
mDataTable.Select("index < 80000 or (content LIKE '%200%')"):
3254.68 ms
dv.RowFilter = "index < 80000":
28120.4352 ms
dv.RowFilter = "index < 80000 or (content LIKE '%200%')":
28200.5504 ms

running 3 times ...
mDataTable.Select("index < 80000"):
410.5904 ms
mDataTable.Select("index < 80000 or (content LIKE '%200%')"):
3244.6656 ms
dv.RowFilter = "index < 80000":
28070.3632 ms
dv.RowFilter = "index < 80000 or (content LIKE '%200%')":
28721.2992 ms

running 4 times ...
mDataTable.Select("index < 80000"):
430.6192 ms
mDataTable.Select("index < 80000 or (content LIKE '%200%')"):
3244.6656 ms
dv.RowFilter = "index < 80000":
27719.8592 ms
dv.RowFilter = "index < 80000 or (content LIKE '%200%')":
27960.2048 ms

running 5 times ...
mDataTable.Select("index < 80000"):
410.5904 ms
mDataTable.Select("index < 80000 or (content LIKE '%200%')"):
3555.112 ms
dv.RowFilter = "index < 80000":
27970.2192 ms
dv.RowFilter = "index < 80000 or (content LIKE '%200%')":
28170.5072 ms

running 6 times ...
mDataTable.Select("index < 80000"):
430.6192 ms
mDataTable.Select("index < 80000 or (content LIKE '%200%')"):
3284.7232 ms
dv.RowFilter = "index < 80000":
28671.2272 ms
dv.RowFilter = "index < 80000 or (content LIKE '%200%')":
28150.4784 ms

running 7 times ...
mDataTable.Select("index < 80000"):
440.6336 ms
mDataTable.Select("index < 80000 or (content LIKE '%200%')"):
3254.68 ms
dv.RowFilter = "index < 80000":
27639.744 ms
dv.RowFilter = "index < 80000 or (content LIKE '%200%')":
27970.2192 ms

running 8 times ...
mDataTable.Select("index < 80000"):
420.6048 ms
mDataTable.Select("index < 80000 or (content LIKE '%200%')"):
3214.6224 ms
dv.RowFilter = "index < 80000":
27389.384 ms
dv.RowFilter = "index < 80000 or (content LIKE '%200%')":
27820.0032 ms

running 9 times ...
mDataTable.Select("index < 80000"):
430.6192 ms
mDataTable.Select("index < 80000 or (content LIKE '%200%')"):
3184.5792 ms
dv.RowFilter = "index < 80000":
27529.5856 ms
dv.RowFilter = "index < 80000 or (content LIKE '%200%')":
27709.8448 ms

running 10 times ...
mDataTable.Select("index < 80000"):
430.6192 ms
mDataTable.Select("index < 80000 or (content LIKE '%200%')"):
3194.5936 ms
dv.RowFilter = "index < 80000":
27339.312 ms
dv.RowFilter = "index < 80000 or (content LIKE '%200%')":
28020.2912 ms

cost [ 596657.952 ms ] to WHILE loop.

--
宝剑锋从磨砺出，梅花香自苦寒来

我欲仗剑走天涯

http://blog.csdn.net/alonesword

posted on 2007-07-01 13:24 孤剑阅读(14743) 评论(3) 编辑收藏举报