Foxtable(狐表)用户栏目专家坐堂 → [分享]大数据文本读取操作


  共有1615人关注过本帖树形打印复制链接

主题:[分享]大数据文本读取操作

帅哥,在线噢!
有点蓝
  1楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:超级版主 帖子:13485 积分:68578 威望:0 精华:5 注册:2015/6/24 9:21:00
[分享]大数据文本读取操作  发帖心情 Post By:2016/10/18 9:42:00 [只看该作者]

前段时间有狐友提了如何导入大量数据的文本文件,就花了点时间整了一个这样的例子,一起讨论讨论。本来只是想做一个简单的例子的,结果测试过程中发现了一些问题,就把测试范围扩大了。


一、准备工作

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、然后随机生成一些测试数据。就以1W10W110W行的数据分别做测试好了。再分别测试本地和云主机数据库的情况。

3、测试配置:

本地:cpuXeon E321308核心,8G内存,64win7旗舰版,狐表2016商业版,sqlserver2008sp4

阿里云:cpuXeon E5-26802核心,4G内存,32win2008标准版, sqlserver2008sp4

网络:20M移动宽带

4、读取比较

1W10W的数据ReadAllText还是可以轻松读取的,本例测试产生的文件大约为1.2M12M。但是110W的数据大概是130MReadAllText读取就出错了。

观察一下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指的是一次向数据库提交的记录数量,可以改为其它数字,下面的测试数据中括号【】中的就是分别一次提交10002000500010000的结果,第二个参数是指提交到本地还是远程数据库,0为本地数据库,其它是远程数据库。数据库连接字符串和文件路径在全局变量设置。

(以下m为分钟,s为秒)


本地数据库

云主机数据库

1万行

10万行

110万行

1万行

10万行

 

15.5s10000

119s1000

23m37s

19.6s10000

185s1000

 

16s10000

121s1000

 

19.1s1000

 

 

15.5s10000

141s5000

 

18s1000

 

 

12.3s1000

140s5000

 

17.7s1000

 

 

15.5s10000

154s10000

 

17.7s5000

213s10000

 

12.8s2000

155s10000

 

18s2000

 

 

9s

95s95s

 

 

 

 


上面是测试结果,测试完只有一个字能表达,就是110万行我只测试了一次,不敢测了,远程数据库的话估计得40分钟左右。表格没有任何事件啊,但是会不会有隐藏事件或者触发判断有没有事件的代码呢,试试加上SystemReady,就是

SystemReady = False

Functions.Execute("ReadData_StreamReader_T",1000,0)

SystemReady = True


测试一看,有效哦。参数10001万行数据降到了9s10万行的降到了95s,有明显提升。

既然这样,是不是用Datatable会更好呢。

 

4.2、使用StreamReader+DataTable

测试函数调用:Functions.Execute("ReadData_StreamReader",1000,0)

本地数据库

云主机数据库

1万行

10万行

110万行

1万行

10万行

110万行

2.7s1000

25s1000

4m20s1000

8.3s1000

79s1000

15m10s1000

2.5s1000

25s1000

 

8.2s1000

78s1000

 

3s2000

35s5000

6m5000

8.2s2000

84s5000

15m47s5000

3.6s5000

36s5000

 

8.8s5000

84s5000

 

4.7s10000

45s10000

 

9.9s10000

95s10000

 

2.2s1000SystemReady = False

19s1000SystemReady = False

10000015m 30W 放弃

 

515s100000

 

 

测试结果有惊喜!本地数据库居然提升了5倍左右的速度。110万行的也敢测试了。


另外发现了几个问题:

1)一次传入1000行数据给数据库的速度居然比一次传入5000行甚至更多的效率快。本地数据库尤其明显,基本接近2倍的差距。按理数据库连接往返的次数少了,应该更快才对的。但是远程数据库又没要这么大的差距。

         仔细思考一下,估计问题应该在TableDataTable插入、读取和删除数据上。就是一次性生成的数据越多,操作效率越低。

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.ClearDataTables("UserInfo").DeleteFor("") DataTables("UserInfo").Savedt.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.4ReadAllText+ DataRow集合

下面是使用ReadAllText一次性读取到内存中,然后再插入数据的数据。结果和StreamReader差不多,说明他们读取数据的速度应该是差不多的。

Functions.Execute("ReadData_AllText",1000,0)

本地数据库(10万行)

云主机数据库(10万行)

2.8s1000

60s1000

2.6s1000

62s1000

2.2s5000

58s5000

2.5s5000

60s5000

2.5s10000

56s10000

2.8s100000

57s100000

2.7sdt2.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、由于时间的关系,本例子也没有去做详尽的测试,只是每种方式测试12次,除非数据偏差太离谱,才会重新测试。

4、在I5 6300+8G+win10的笔记本上做本地测试的数据比上面的数据更快,所以硬件的区别也是明显的。

5、测试过程中sqlserver的内存使用一直保存在一个稳定的状态,说明sqlserver的内存管理还是做的很好的。

6sqlserver在创建数据库的时候,是可以指定数据库文件的增长值的,默认是10M。当数据库存储的大小超过这个值的时候就会自动增长,而增长的时候是会对数据库的操作存在一定的影响的。建议在使用的时候更改这个值,具体多大要根据自己项目情况考虑了。

7、上面的测试可以看到本地和远程数据库的明显差距,这个差距有没有办法缩小呢(不考虑硬件和网络)?答案是肯定的,那具体有什么办法实现呢?留给狐友思考一下

8、本地测试110W的数据已经达到二十几秒了,那么还能不能再提升呢?

9、现在是一百多M的文件,如果是1G2G甚至更大,StreamReader还能胜任么?



 下载信息  [文件大小:   下载次数: ]
点击浏览该文件:大文本文件读取.table


[此贴子已经被作者于2016/10/19 0:22:01编辑过]

[本帖被加为精华]
 回到顶部
帅哥哟,离线,有人找我吗?
Hopenight
  2楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:一尾狐 帖子:430 积分:4150 威望:0 精华:0 注册:2016/5/6 14:34:00
  发帖心情 Post By:2016/10/18 9:53:00 [只看该作者]

Very Good!

 回到顶部
帅哥哟,离线,有人找我吗?
bahamute
  3楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:五尾狐 帖子:1176 积分:8899 威望:0 精华:0 注册:2008/9/1 22:20:00
  发帖心情 Post By:2016/10/18 9:56:00 [只看该作者]

好精辟的文章,看来大数据量还是要多考虑DataTable,顶版主!

 回到顶部
帅哥哟,离线,有人找我吗?
blackzhu
  4楼 | QQ | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信 一级勋章
等级:MVP荣誉狐 帖子:8938 积分:51222 威望:0 精华:16 注册:2008/9/1 9:45:00
  发帖心情 Post By:2016/10/21 13:58:00 [只看该作者]

 Functions.Execute("ReadData_StreamReader",1000,1)   0 是本地 1 是远程?


我在命令窗口执行了下,显示属性未初始化的错误

 回到顶部
帅哥哟,离线,有人找我吗?
blackzhu
  5楼 | QQ | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信 一级勋章
等级:MVP荣誉狐 帖子:8938 积分:51222 威望:0 精华:16 注册:2008/9/1 9:45:00
  发帖心情 Post By:2016/10/21 14:21:00 [只看该作者]

如果是EXCEL 怎么一次性导入到前台,你现在用的文本文件用流读取的.

 回到顶部
帅哥哟,离线,有人找我吗?
blackzhu
  6楼 | QQ | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信 一级勋章
等级:MVP荣誉狐 帖子:8938 积分:51222 威望:0 精华:16 注册:2008/9/1 9:45:00
  发帖心情 Post By:2016/10/21 14:32:00 [只看该作者]

 我将EXCEL 数据导出到 txt再 导入,只能导入第一列的数据.格式是这样的

A列  B列
XX   XX
YY    YY

 回到顶部
帅哥哟,离线,有人找我吗?
blackzhu
  7楼 | QQ | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信 一级勋章
等级:MVP荣誉狐 帖子:8938 积分:51222 威望:0 精华:16 注册:2008/9/1 9:45:00
  发帖心情 Post By:2016/10/21 15:09:00 [只看该作者]

 Functions.Execute("ReadData_AllText",1000,0) 也会出现异常

 回到顶部
帅哥,在线噢!
有点蓝
  8楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:超级版主 帖子:13485 积分:68578 威望:0 精华:5 注册:2015/6/24 9:21:00
  发帖心情 Post By:2016/10/21 15:10:00 [只看该作者]

回4楼,默认是没有文本数据,可以调用CreateLocalData创建,不过会同时写数据库,可以自己改改

0 是本地 1 是远程,远程需要改回自己的数据库链接,在全局变量C2

 回到顶部
帅哥,在线噢!
有点蓝
  9楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:超级版主 帖子:13485 积分:68578 威望:0 精华:5 注册:2015/6/24 9:21:00
  发帖心情 Post By:2016/10/21 15:23:00 [只看该作者]

回6楼,我例子是以逗号分隔的,你是空格的,自己改改。



 回到顶部
帅哥哟,离线,有人找我吗?
blackzhu
  10楼 | QQ | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信 一级勋章
等级:MVP荣誉狐 帖子:8938 积分:51222 威望:0 精华:16 注册:2008/9/1 9:45:00
  发帖心情 Post By:2016/10/21 15:30:00 [只看该作者]

 有没有EXCEL的例子导入到数据库.


第二个 我现在在EXCEL 导出到 CSV是逗号的,但是导入到8000行的时候 一直显示是索引 不对


第三个  经常发现4.3的错误.

 回到顶部
总数 20 1 2 下一页