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

大量数据插入几种方式速度的比较在设计数据库里，我们就应该去数据库设计的性能进行评估。但是在经验不足的情况下，就需要快速建立一个和真实环境相近的数据库，进行性能测试。在这个过程中，遇到的第一个问题就是如何快速地插入千万级数据。一个新手可能会写出下面的代码。（索引未建） 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 intSet I = 0Begin TranInsertData:Insert into dbo.Simple values (I, RAND() * 2000000000)Set I = I + 1If I 100000 Goto InsertDataCommit Tran注：BEGIN TRAN标记事务开始COMMIT TRAN 提交事务一般把DML语句(select ,delete,update,insert语句)放在BEGIN TRAN.COMMIT TRAN 之间作为一个事务处理也就是那些语句执行过程中如果遇到错误，无论哪句遇到错误，所有语句都会回滚到BEGIN TRAN之前的状态。这个是事务处理。例如你需要更新一个数据，但是这个数据存在于两个表内，你需要用两个update语句。如果第一个执行成功，第二个由于某种原因执行失败，将导致这个数据的不一致。数据库的数据就出错了。如果想要避免，就用BEGIN TRAN.COMMIT TRAN ，把这两个update写在中间，如果发生如上的事情，第一个update的数据，将被退回，即没有被更新。你收到错误信息，就知道更新完全没有进行，不需要考虑部分更新的情况。还有一个方法就是拼SQL，因为之前都是一个Insert语句插入一条数据，但是Insert是可以一次插入多条的啊。可以定义一个varchar(8000)变量，然后把要插入的假数据接在后面。最后用EXEC运行。如下。Declare I intDECLARE sql varchar(8000)Set I = 0ResetSql: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 = 299Begin exec (sql) Set I = I + 1 Goto ResetSqlEndSet I = I + 1If I 100000Begin Set sql = sql + , Goto ComInsertEnd怎么这么复杂？首先因为varchar(8000)放不下所有的数据，所以要分批插入。而且简单起见上面的代码并没有做到准确地插入1000万条，而是插入了9999900条。这个比第二种方式稍稍快一点。用时15分钟。不过为了这么点性能，多写这么多代码，感觉还是不太值得，除非要是插入上亿数据，省下半小时时间还是值得的。另外，如果需要多次清空、重新插入。那么把之前的数据导出到TEXT文件里会更快一些，如上文所说，导入1000万数据要10分钟。导出也只要2分钟。导出的代码如下：EXEC master.dbo.sp_configure show advanced options, 1RECONFIGUREEXEC master.dbo.sp_configure xp_cmdshell, 1RECONFIGURE 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)当需要往sql server里插入大量数据时，尤其是每条数据都不相同的时候，循环使用insert效率就会很低，这时尝试先将数据写入txt文本文件，再使用bulk insert来执行插入，速度大大的快。StreamWriter sw = new StreamWriter(HttpContext.Current.Server.MapPath(pages/TextFile.txt);while（条件）StringBuilder sb = new StringBuilder();/构造数据 /./ENDsw.WriteLine(sb.ToString();sw.Flush();sw.Close();string bulksql = BULK INSERT product FROM d:/fx/pages/TextFile.txt WITH (FIELDTERMINATOR = ,ROWTERMINATOR = n);/执行bulksql语句。ok，数据是插进去了，可是问题来了，当txt文件中存在中文的的时候，插入到数据库中时就会变成了乱码（我这儿数据库中存放中文的字段是Nvarchar类型的）。解决办法，将写入txt的文本编码方式变一下。然后bulksql中再加一个DATAFILETYPE=widechar就可以解决啦。如下：StreamWriter sw = new StreamWriter(HttpContext.Current.Server.MapPath(pages/TextFile.txt),false,Encoding.Default);while（条件）StringBuilder sb = new StringBuilder();/构造数据 /./ENDsw.WriteLine(sb.ToString();sw.Flush();sw.Close();