前段时间有狐友提了如何导入大量数据的文本文件，就花了点时间整了一个这样的例子，一起讨论讨论。本来只是想做一个简单的例子的，结果测试过程中发现了一些问题，就把测试范围扩大了。
一、准备工作
1、先来创建一个测试数据库（DataTest）和测试表（UserInfo）
USE [DataTest]
GO
CREATE TABLE [dbo].[UserInfo_Tmp](
[_Identify] [int] IDENTITY(1,1) NOT NULL,
[_Locked] [bit] NULL,
[_SortKey] [numeric](28, 14) NULL,
[ID] [nvarchar](36) NULL,
[CreatedTime] [datetime] NULL,
[UserName] [nvarchar](20) NULL,
[Phone] [nvarchar](16) NULL,
[Account] [nvarchar](30) NULL,
[Balance] [float] NULL,
[Description] [nvarchar](500) NULL,
CONSTRAINT [PrimaryKey_UserInfo_Tmp] PRIMARY
KEY NONCLUSTERED
(
[_Identify] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
2、然后随机生成一些测试数据。就以1W、10W和110W行的数据分别做测试好了。再分别测试本地和云主机数据库的情况。
3、测试配置：
本地：cpu（Xeon E32130）8核心，8G内存，64位win7旗舰版，狐表2016商业版，sqlserver2008sp4
阿里云：cpu（Xeon E5-2680）2核心，4G内存，32位win2008标准版，
sqlserver2008sp4
网络：20M移动宽带
4、读取比较
1W和10W的数据ReadAllText还是可以轻松读取的，本例测试产生的文件大约为1.2M和12M。但是110W的数据大概是130M，ReadAllText读取就出错了。
观察一下ReadAllText返回的是string类型，从string类型的length属性为integer整型可以得知，string类型最大能存储的字节长度是2147483647，接近30M的内容。所以基本超过这个值的文本用ReadAllText读取就会抛出内存溢出的错误提示了。
所以超过这个值的文本就只能通过流的方式来读取了，.net提供了一个StreamReader类型，可以逐行，也可以按指定的长度分块读取文件内容，下面我们就来测试一下这个StreamReader到底怎么样。
使用上是很简单的，如
Using sr As IO.StreamReader = New IO.StreamReader("d:\123.txt") '直接从文件路径生成'
Dim line As String = sr.ReadLine() '读取一行
Do While line IsNot Nothing '如果不为空.为空说明读取完毕,结束循环
'其它处理
line = sr.ReadLine() '读取下一行
Loop
End Using
下面就测试把数据提取转存到数据库中，为了不让测试过程太过于难受，考虑了一下还是把数据存到sqlserver数据库，这里使用了论坛提起过的SqlBulkCopy，以加快存储的速度，同时顺便测试一下SqlBulkCopy的性能。
4.1、使用StreamReader+Table
SqlBulkCopy需要一个Datatable作为参数，所以我们就先把读取的数据放到table中，在项目中创建一个结构和UserInfo_Tmp一样的内部表，然后就开始测试，测试函数调用如下：
Functions.Execute("ReadData_StreamReader_T",1000,0)
参数中的1000指的是一次向数据库提交的记录数量，可以改为其它数字，下面的测试数据中括号【】中的就是分别一次提交1000，2000，5000，10000的结果，第二个参数是指提交到本地还是远程数据库，0为本地数据库，其它是远程数据库。数据库连接字符串和文件路径在全局变量设置。
（以下m为分钟，s为秒）
|
本地数据库
|
云主机数据库
|
|
1万行
|
10万行
|
110万行
|
1万行
|
10万行
|
|
|
15.5s【10000】
|
119s【1000】
|
23m37s
|
19.6s【10000】
|
185s【1000】
|
|
|
16s【10000】
|
121s【1000】
|
|
19.1s【1000】
|
|
|
|
15.5s【10000】
|
141s【5000】
|
|
18s【1000】
|
|
|
|
12.3s【1000】
|
140s【5000】
|
|
17.7s【1000】
|
|
|
|
15.5s【10000】
|
154s【10000】
|
|
17.7s【5000】
|
213s【10000】
|
|
|
12.8s【2000】
|
155s【10000】
|
|
18s【2000】
|
|
|
|
9s
|
95s，95s
|
|
|
|
|
上面是测试结果，测试完只有一个字能表达，就是“慢”！110万行我只测试了一次，不敢测了，远程数据库的话估计得40分钟左右。表格没有任何事件啊，但是会不会有隐藏事件或者触发判断有没有事件的代码呢，试试加上SystemReady，就是
SystemReady = False
Functions.Execute("ReadData_StreamReader_T",1000,0)
SystemReady = True
测试一看，有效哦。参数1000的1万行数据降到了9s，10万行的降到了95s，有明显提升。
既然这样，是不是用Datatable会更好呢。
4.2、使用StreamReader+DataTable
测试函数调用：Functions.Execute("ReadData_StreamReader",1000,0)
|
本地数据库
|
云主机数据库
|
|
1万行
|
10万行
|
110万行
|
1万行
|
10万行
|
110万行
|
|
2.7s【1000】
|
25s【1000】
|
4m20s【1000】
|
8.3s【1000】
|
79s【1000】
|
15m10s【1000】
|
|
2.5s【1000】
|
25s【1000】
|
|
8.2s【1000】
|
78s【1000】
|
|
|
3s【2000】
|
35s【5000】
|
6m【5000】
|
8.2s【2000】
|
84s【5000】
|
15m47s【5000】
|
|
3.6s【5000】
|
36s【5000】
|
|
8.8s【5000】
|
84s【5000】
|
|
|
4.7s【10000】
|
45s【10000】
|
|
9.9s【10000】
|
95s【10000】
|
|
|
2.2s【1000】SystemReady = False
|
19s【1000】SystemReady = False
|
【100000】15m 30W 放弃
|
|
515s【100000】
|
|
测试结果有惊喜！本地数据库居然提升了5倍左右的速度。110万行的也敢测试了。
另外发现了几个问题：
1）一次传入1000行数据给数据库的速度居然比一次传入5000行甚至更多的效率快。本地数据库尤其明显，基本接近2倍的差距。按理数据库连接往返的次数少了，应该更快才对的。但是远程数据库又没要这么大的差距。
仔细思考一下，估计问题应该在Table和DataTable插入、读取和删除数据上。就是一次性生成的数据越多，操作效率越低。
2）加上SystemReady仍然有效，就是说用Datatable还是会有事件/代码触发
既然这样，还有没有方法避免这种情况呢？下面我们就来测试另外一种方法
4.3、使用StreamReader+System.Data.DataRow集合
Datatable有个BaseTable，是.net的基础类型，那么我们可不可以直接在BaseTable中添加行，而不通过狐表的Datatable呢，测试是可行的，而且速度还有了4倍的提升，相对于使用table来说则是有了20倍的提升。下面是测试数据
测试函数调用：Functions.Execute("ReadData_StreamReader_Rows",1000,0)
|
参数
|
模式
|
本地数据库
|
云主机数据库
|
|
1万行
|
10万行
|
110万行
|
1万行
|
10万行
|
110万行
|
|
【1000】
|
|
0.53s, 0.54s
|
5.3s, 5.4s
|
55s,56s
|
5.9s, 6s
|
60s,59s
|
|
|
Ready
|
0.35s
|
3.3s, 3.6s
|
35s,34s
|
5.8s,5.4s
|
55s,55s
|
|
|
[clone]
|
0.3s
|
2.6s,2.6s
|
28s,30s
|
5.7s,5.7s
|
56s,56s
|
10m28
|
|
【5000】
|
|
0.53s, 0.56s
|
5.1s, 5.3s
|
59s,57s
|
5.2s, 5.4s
|
53s,58s
|
|
|
Ready
|
0.27s
|
3.1s,3s
|
32s,32s
|
5.5s,5.3s
|
52s,54s
|
|
|
[clone]
|
0.29s
|
2.8s,2.5s
|
|
5.4s,5.4s
|
51s,52s
|
9m52s
|
|
【10000】
|
|
0.55s, 0.61s
|
5.6s, 5.4s
|
62s,64s
|
5.1s, 5.1s
|
52s,53s
|
|
|
Ready
|
0.31s
|
3.5s,3.4s
|
36s,36s
|
4.9s,5s
|
53s,54s
|
|
|
[clone]
|
0.28s
|
2.5s,2.4s
|
31s,30s
|
5.4s,5.3s
|
54s,53s
|
9m35s
|
|
【100000】
|
|
|
5.1s,4.8s
|
60s
|
|
56s
|
|
|
Ready
|
|
2.8s,3s
|
32s
|
|
53s
|
|
|
[clone]
|
|
2.5s,2s
|
24s
|
|
52s,
|
9m19s
|
|
【1100000】
|
[clone]
|
|
|
23s
|
|
|
|
这次测试有另外一个比较重大的发现，就是在测试110万行数据的时候，狐表的内存一下子飙升到500M左右，如果再测试第二次，就会在中途出现内存溢出的错误了，这时狐表的内存在943M左右。看看系统内存8G只使用了4.5G，内存还没有满的，测试了几次都这样。会不会狐表内存在960M左右的时候就会超出某个类型的极限，就像string最多只能存储30M的数据一样呢？
后来在userinfo表试增加一行，结果出现了“索引超出了数组界限。”的错误提示，无法增加行，显然在这个表增加行的时候，内部某个数组或者集合使用的索引超出计算范围了。再尝试用代码DataTables("UserInfo").AddNew增加行，就出现以下这样一个提示，但是我是每增加固定的行数，比如增加1000行，然后保存到sqlserver，接着清空，然后再增加，这样的话userinfo表最多也就1000行数据。数据库也就是存储不到220W行。
此主题相关图片如下：1.jpg
尝试代码增加DataTables("UserInfo").DataRows.Clear，DataTables("UserInfo").DeleteFor("")
DataTables("UserInfo").Save，dt.Rows.Clear等等都没有效果，一样的错误。
在命令窗口执行以下代码，出现另外一个错误：
DataTables("UserInfo").DeleteFor("")
DataTables("UserInfo").save
DataTables("UserInfo").AddNew
此主题相关图片如下：2.jpg
测试使用GC.Collect()也没有多大起色，用System.Diagnostics.Process.GetCurrentProcess().
MinWorkingSet = new System.IntPtr(5)貌似可以，但是一测试函数调用，内存马上坐火箭追上来了，也不行。一个现象是调用MinWorkingSet 后内存为36M，但是调用GC.Collect()后马上回升到800M左右，所以这玩意就是个假象。
再想想，.net内存不能回收一般都是因为还存在引用，system.Data.DataRow-》BaseTable-》Datatable，难道BaseTable增加的行，尽管没有添加到表格中，直接添加到集合里，那么在集合清空后，实际还存在弱引用？那么能不能不通过BaseTable来增加行，直接增加和表格没有任何关联的行呢？这个问题留给狐友自己测试一下。我用了另外一个偷懒的方法，就是
BaseTable.clone，这样就拷贝克隆了一个表结构一样，却不会和其它对象引用有任何关联的临时表了，这回内存回收应该有效了吧。一测试，果然，函数调用完后用GC.Collect马上就变成60M左右的使用量了，而且多次测试110万行数据也没有问题了，内存不会追上来，不用老是重启项目。不过改BaseTable.clone之前生成的内存还是不能回收。
最意外的是速度居然快了，比没有使用SystemReady差不多有80%到一倍的提升。上面表格中模式为clone的数据就是使用BaseTable.clone后的测试结果。
另外一个现象是没有使用BaseTable.clone的话加上SystemReady仍然有效，可以看上面模式为Ready的数据。和使用clone的情况基本相当。
而且明显SqlBulkCopy的同时处理大批量的优势出来了，一次性扔给数据库的数量多的情况下速度有一定的提升。
4.4、ReadAllText+ DataRow集合
下面是使用ReadAllText一次性读取到内存中，然后再插入数据的数据。结果和StreamReader差不多，说明他们读取数据的速度应该是差不多的。
Functions.Execute("ReadData_AllText",1000,0)
|
本地数据库（10万行）
|
云主机数据库（10万行）
|
|
2.8s【1000】
|
60s【1000】
|
|
2.6s【1000】
|
62s【1000】
|
|
2.2s【5000】
|
58s【5000】
|
|
2.5s【5000】
|
60s【5000】
|
|
2.5s【10000】
|
56s【10000】
|
|
2.8s【100000】
|
57s【100000】
|
|
2.7s【dt】2.5s
|
|
|
|
|
结论：
1、尽量使用datatable来操作数据，而少用table；加上datatable. StopRedraw可有效提高性能。大量数据操作的时候或者干脆隐藏掉对应的table，实测隐藏table比使用datatable. StopRedraw效果好。
2、如果不需要触发事件，费时间的处理加上SystemReady屏蔽事件试试，会有意想不到的效果
3、狐表表格不要一次性加载太多的数据进行处理，尽量分页。
4、不考虑内存和其它逻辑处理的情况下，SqlBulkCopy尽量一次导入的数据越多效率越高（当然这个多到什么样的数据量应该也是有一定限制的，具体这里不做测试了）
5、写大数据的时候，狐表的WriteAllText不会有性能和内存上的瓶颈，不过记得设置append=true，几百M的文本文件追加数据都是秒写，这个毫无压力
6、超过30M的文本读取还是使用StreamReader吧。
其它：
1、这个例子算是比较极端的，开始主要是想测试读取大文本文件，后来因为测试中发现的一些问题才逐渐扩大范围并寻求解决方案。所以不一定适合作为常规应用，还应结合自己项目的实际情况进行分析使用
2、例子本身不是重点，碰到问题并思考和解决问题的思路才是重点
3、由于时间的关系，本例子也没有去做详尽的测试，只是每种方式测试1到2次，除非数据偏差太离谱，才会重新测试。
4、在I5 6300+8G+win10的笔记本上做本地测试的数据比上面的数据更快，所以硬件的区别也是明显的。
5、测试过程中sqlserver的内存使用一直保存在一个稳定的状态，说明sqlserver的内存管理还是做的很好的。
6、sqlserver在创建数据库的时候，是可以指定数据库文件的增长值的，默认是10M。当数据库存储的大小超过这个值的时候就会自动增长，而增长的时候是会对数据库的操作存在一定的影响的。建议在使用的时候更改这个值，具体多大要根据自己项目情况考虑了。
7、上面的测试可以看到本地和远程数据库的明显差距，这个差距有没有办法缩小呢（不考虑硬件和网络）？答案是肯定的，那具体有什么办法实现呢？留给狐友思考一下
8、本地测试110W的数据已经达到二十几秒了，那么还能不能再提升呢？
9、现在是一百多M的文件，如果是1G、2G甚至更大，StreamReader还能胜任么？
[此贴子已经被作者于2016/10/19 0:22:01编辑过]
加好友 
发短信
Post By：2016/10/18 9:42:00 [只看该作者]
[本帖被加为精华]
