大量数据插入的几种方式的速度比较

在设计数据库里，我们就应该去数据库设计的性能进行评估。但是在经验不足的情况下，就需要快速建立一个和真实环境相近的数据库，进行性能测试。在这个过程中，遇到的第一个问题就是如何快速地插入千万级数据。

一个新手可能会写出下面的代码。（索引未建）

 

Insert into dbo.Simple values (@I, RAND() * 2000000000)

 

然后在外面加个循环。大概测试了一个，10万数据，用时100秒。那么1000万数据，就要用时1万秒。大约是将近3个小时。（好吧，我知道我的电脑很烂。服务里上只要1个小时20分钟。）这个速度是难以让人接受的。因为拥有1000万数据的数据库，直接从文件导入数据，大约只需要不到10分钟。我们希望造假数据的速度和这个时间在同一个单位级别上吧。

 

其实最简单的办法，就是在刚才写好的语句前加个Begin Tran然后结尾加个Commit Tran就可以了。这个方式插入1000万条数据，大约用时18.5分钟。代码如下：

 

Declare @I int

Set @I = 0

Begin Tran

InsertData:

Insert into dbo.Simple values (@I, RAND() * 2000000000)

Set @I = @I + 1

If @I < 100000

    Goto InsertData

Commit Tran

 

还有一个方法就是拼SQL，因为之前都是一个Insert语句插入一条数据，但是Insert是可以一次插入多条的啊。可以定义一个varchar(8000)变量，然后把要插入的假数据接在后面。最后用EXEC运行。如下。

 

Declare @I int

DECLARE @sql varchar(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分钟。

 

导出的代码如下：

 

EXEC master.dbo.sp_configure 'show advanced options', 1

RECONFIGURE

EXEC master.dbo.sp_configure 'xp_cmdshell', 1

RECONFIGURE

 

EXEC xp_cmdshell 'bcp "SELECT * FROM Sample.dbo.Simple" queryout "C:"data.txt" -T -S(local)"SQLEXPRESS -c -t,'

 

生成的Text文件是以逗号将每列分开的一列一行纯文本文件。

 

导入的代码如下：

 

bulk insert dbo.Simple from 'C:"data.txt' with(fieldterminator=',', rowterminator='"n')

 

不知道大家有更快的方法吗？

2009年9月14日更新：

发现自己还是基础太差了，书上就有的方法自己在这里研究这么半天。上面导文件的方法也需要10分钟，下面这个方法，纯SQL，只需要5分钟，就可以生成1000万数据。

IF OBJECT_ID('dbo.Nums') IS NOT NULL
  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