大量数据插入的几种方式的速度比较
来源:互联网 发布:淘宝网童装模特 编辑:程序博客网 时间:2024/04/26 00:52
来自:http://www.cnblogs.com/nankezhishi/archive/2009/08/15/bulkinsert.html
在设计数据库里,我们就应该去数据库设计的性能进行评估。但是在经验不足的情况下,就需要快速建立一个和真实环境相近的数据库,进行性能测试。在这个过程中,遇到的第一个问题就是如何快速地插入千万级数据。
一个新手可能会写出下面的代码。(索引未建)
Insertinto dbo.Simple values (@I,RAND() * 2000000000)
然后在外面加个循环。大概测试了一个,10万数据,用时100秒。那么1000万数据,就要用时1万秒。大约是将近3个小时。(好吧,我知道我的电脑很烂。服务里上只要1个小时20分钟。)这个速度是难以让人接受的。因为拥有1000万数据的数据库,直接从文件导入数据,大约只需要不到10分钟。我们希望造假数据的速度和这个时间在同一个单位级别上吧。
其实最简单的办法,就是在刚才写好的语句前加个BeginTran然后结尾加个CommitTran就可以了。这个方式插入1000万条数据,大约用时18.5分钟。代码如下:
Declare @Iint
Set @I= 0
BeginTran
InsertData:
Insertinto dbo.Simple values (@I,RAND() * 2000000000)
Set @I= @I + 1
If @I< 100000
Goto InsertData
CommitTran
还有一个方法就是拼SQL,因为之前都是一个Insert语句插入一条数据,但是Insert是可以一次插入多条的啊。可以定义一个varchar(8000)变量,然后把要插入的假数据接在后面。最后用EXEC运行。如下。
Declare @Iint
DECLARE @sqlvarchar(8000)
Set @I= 0
ResetSql:
Set @sql= 'Insert into dbo.Simple values '
ComInsert:
Set @sql= @sql + '(' + CONVERT(varchar(10), @I)+ ',' + CONVERT(varchar(10),Convert(int,RAND() * 2000000000)) +')'
If @I% 300 = 299
Begin
exec (@sql)
Set @I = @I + 1
Goto ResetSql
End
Set @I= @I + 1
If @I< 100000
Begin
Set @sql = @sql +','
Goto ComInsert
End
怎么这么复杂?首先因为varchar(8000)放不下所有的数据,所以要分批插入。而且简单起见上面的代码并没有做到准确地插入1000万条,而是插入了9999900条。
这个比第二种方式稍稍快一点。用时15分钟。不过为了这么点性能,多写这么多代码,感觉还是不太值得,除非要是插入上亿数据,省下半小时时间还是值得的。
另外,如果需要多次清空、重新插入。那么把之前的数据导出到TEXT文件里会更快一些,如上文所说,导入1000万数据要10分钟。导出也只要2分钟。
导出的代码如下:
EXECmaster.dbo.sp_configure'show advanced options', 1
RECONFIGURE
EXECmaster.dbo.sp_configure'xp_cmdshell', 1
RECONFIGURE
EXECxp_cmdshell 'bcp "SELECT * FROM Sample.dbo.Simple" queryout "C:"data.txt" -T -S(local)"SQLEXPRESS -c -t,'
生成的Text文件是以逗号将每列分开的一列一行纯文本文件。
导入的代码如下:
bulkinsert dbo.Simplefrom 'C:"data.txt'with(fieldterminator=',',rowterminator='"n')
不知道大家有更快的方法吗?
2009年9月14日更新:
发现自己还是基础太差了,书上就有的方法自己在这里研究这么半天。上面导文件的方法也需要10分钟,下面这个方法,纯SQL,只需要5分钟,就可以生成1000万数据。
DROP TABLE dbo.Nums;
GO
CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
DECLARE @max AS INT, @rc AS INT;
SET @max = 5000000;
SET @rc = 1;
INSERT INTO Nums VALUES(1);
WHILE @rc * 2 <= @max
BEGIN
INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums;
SET @rc = @rc * 2;
END
INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums WHERE n + @rc <= @max;
--以上函数取自Inside SQL Server 2005: T-SQL Query一书。
INSERT dbo.Sample SELECT n, RAND(CAST(NEWID() AS BINARY(16))) FROM Nums
- 大量数据插入的几种方式的速度比较
- 三种方法ASP读取大量数据的速度比较
- 插入大量数据速度慢的解决方法:批量插入
- Delphi中ADO处理数据的几种方式的速度比较
- 插入大量数据至MongoDB数据库的速度问题分析
- SQL插入数据的几种方式
- 存储数据的几种方式比较
- 数据写入文件的几种方法速度比较
- 几种语言的执行速度比较
- 提高Sqlserver大批量插入数据速度的几点方法
- Oracle insert 插入数据的几种方式
- 优化网站速度的几种方式
- 优化网站速度的几种方式
- as3比较存取数据的几种方式
- redis快速的插入大量的数据
- 使用insert插入大量数据的总结
- Oracle如何快速、大量的插入数据
- redis 大量数据的插入处理
- Pear Linux 6.1 发布,法语的桌面 Linux 系统
- 电梯调度算法
- ODBC数据源参数
- Javascript中事件的理解
- java面向对象上:数组
- 大量数据插入的几种方式的速度比较
- ASP.NET C# 获取浏览器信息
- 算法之美——排序算法总结
- 平庸和个性是做人的两个极端
- 重点并不在这个男人爱不爱你
- MFC 程序入口和执行流程
- jQuery实现仿QQ相册功能
- SolusOS 2 Alpha 6 发布,桌面 Linux 发行
- 随机数生成