以文本方式查看主题 - Foxtable(狐表) (http://www.foxtable.com/bbs/index.asp) -- 专家坐堂 (http://www.foxtable.com/bbs/list.asp?boardid=2) ---- [分享]大数据文本读取操作 (http://www.foxtable.com/bbs/dispbbs.asp?boardid=2&id=91737) |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
-- 作者:有点蓝 -- 发布时间: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、然后随机生成一些测试数据。就以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为秒)
上面是测试结果,测试完只有一个字能表达,就是“慢”!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)
测试结果有惊喜!本地数据库居然提升了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)
这次测试有另外一个比较重大的发现,就是在测试110万行数据的时候,狐表的内存一下子飙升到500M左右,如果再测试第二次,就会在中途出现内存溢出的错误了,这时狐表的内存在943M左右。看看系统内存8G只使用了4.5G,内存还没有满的,测试了几次都这样。会不会狐表内存在960M左右的时候就会超出某个类型的极限,就像string最多只能存储30M的数据一样呢? 后来在userinfo表试增加一行,结果出现了“索引超出了数组界限。”的错误提示,无法增加行,显然在这个表增加行的时候,内部某个数组或者集合使用的索引超出计算范围了。再尝试用代码DataTables("UserInfo").AddNew增加行,就出现以下这样一个提示,但是我是每增加固定的行数,比如增加1000行,然后保存到sqlserver,接着清空,然后再增加,这样的话userinfo表最多也就1000行数据。数据库也就是存储不到220W行。 尝试代码增加DataTables("UserInfo").DataRows.Clear,DataTables("UserInfo").DeleteFor("")
DataTables("UserInfo").Save,dt.Rows.Clear等等都没有效果,一样的错误。 在命令窗口执行以下代码,出现另外一个错误: DataTables("UserInfo").DeleteFor("") DataTables("UserInfo").save DataTables("UserInfo").AddNew
测试使用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)
结论: 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编辑过]
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
-- 作者:Hopenight -- 发布时间:2016/10/18 9:53:00 -- Very Good! |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
-- 作者:bahamute -- 发布时间:2016/10/18 9:56:00 -- 好精辟的文章,看来大数据量还是要多考虑DataTable,顶版主! |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
-- 作者:blackzhu -- 发布时间:2016/10/21 13:58:00 -- Functions.Execute("ReadData_StreamReader",1000,1) 0 是本地 1 是远程? 我在命令窗口执行了下,显示属性未初始化的错误 |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
-- 作者:blackzhu -- 发布时间:2016/10/21 14:21:00 -- 如果是EXCEL 怎么一次性导入到前台,你现在用的文本文件用流读取的. |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
-- 作者:blackzhu -- 发布时间:2016/10/21 14:32:00 -- 我将EXCEL 数据导出到 txt再 导入,只能导入第一列的数据.格式是这样的 A列 B列 XX XX YY YY |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
-- 作者:blackzhu -- 发布时间:2016/10/21 15:09:00 -- Functions.Execute("ReadData_AllText",1000,0) 也会出现异常 |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
-- 作者:有点蓝 -- 发布时间:2016/10/21 15:10:00 -- 回4楼,默认是没有文本数据,可以调用CreateLocalData创建,不过会同时写数据库,可以自己改改 0 是本地 1 是远程,远程需要改回自己的数据库链接,在全局变量C2
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
-- 作者:有点蓝 -- 发布时间:2016/10/21 15:23:00 -- 回6楼,我例子是以逗号分隔的,你是空格的,自己改改。 |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
-- 作者:blackzhu -- 发布时间:2016/10/21 15:30:00 -- 有没有EXCEL的例子导入到数据库. 第二个 我现在在EXCEL 导出到 CSV是逗号的,但是导入到8000行的时候 一直显示是索引 不对 第三个 经常发现4.3的错误. |