按照网上的资料进行的百万级循环插入的效率分析循环插入数据库90万条数据的代码

一循环插入数据库90万条数据的代码如下

  TRUNCATE TABLE dbo.People 

  GO

          ---以下两句用于清楚sqlserver的数据跟缓存线上的服务器不建议使用

  DBCC DROPCLEANBUFFERS 

  DBCC FREEPROCCACHE

 

  SET STATISTICS IO ON;

  SET STATISTICS TIME ON;

  GO

          

  DECLARE @d Datetime

          SET @d=GETDATE();

          

  DECLARE @i INT =1

          

  WHILE @i<900000

  BEGIN 

  INSERT INTO dbo.People

          (name, sex, age)

  SELECT 'preson'+cast(@i as varchar(10)),

  CASE WHEN @i%2=0 THEN 0

  WHEN @i%2=1 THEN 1

  END

  ,

  CAST(RAND()*10 AS INT)%10+(@i%26)

  SET @i=@i+1

  END

                  

  SELECT [语句执行花费时间(毫秒)]=DATEDIFF(ms,@d,GETDATE())

 

  SET STATISTICS IO OFF;

  SET STATISTICS TIME OFF;

  GO

          

 

 

 

1未使用set nocount on 和  TRANSACTION

 

使用的时间为614280秒；

 

 

2使用了set nocount on 和  TRANSACTION

 TRUNCATE TABLE dbo.People 

  GO

          ---以下两句用于清楚sqlserver的数据跟缓存线上的服务器不建议使用

  DBCC DROPCLEANBUFFERS 

  DBCC FREEPROCCACHE

 

  SET STATISTICS IO ON;

  SET STATISTICS TIME ON;

  GO

          

  DECLARE @d Datetime

          SET @d=GETDATE();

  SET NOCOUNT ON

          

  DECLARE @i INT =1

  BEGIN TRANSACTION

          

  WHILE @i<900000

  BEGIN 

  INSERT INTO dbo.People

          (name, sex, age)

  SELECT 'preson'+cast(@i as varchar(10)),

  CASE WHEN @i%2=0 THEN 0

  WHEN @i%2=1 THEN 1

  END

  ,

  CAST(RAND()*10 AS INT)%10+(@i%26)

  SET @i=@i+1

  END

                  COMMIT

  SELECT [语句执行花费时间(毫秒)]=DATEDIFF(ms,@d,GETDATE())

 

  SET STATISTICS IO OFF;

  SET STATISTICS TIME OFF;

  GO

          

 

 

使用的时间为267566秒；

 

 

3.使用了with as 

  代码如下

  TRUNCATE TABLE dbo.People 

  GO

          ---以下两句用于清楚sqlserver的数据跟缓存线上的服务器不建议使用

  DBCC DROPCLEANBUFFERS 

  DBCC FREEPROCCACHE

 

  SET STATISTICS IO ON;

  SET STATISTICS TIME ON;

  GO

          

  DECLARE @d Datetime

          SET @d=GETDATE();

 

  /**用with as  */

  WITH Seq(id,name,sex,age) AS

          (

  SELECT 1,

  'preson'+cast(1 AS VARCHAR(10)),

  1,

  CAST(RAND()*10 AS INT)%10

  UNION ALL

          SELECT id+1,

   'preson'+cast(id+1 as varchar(10)),

 

  CASE WHEN id%2=0 THEN 0

  WHEN id%2=1 THEN 1

  END

  ,

  CAST(RAND()*10 AS INT)%10+(id%26)

  FROM Seq

  WHERE id<900000

  

  

  

  )

  INSERT INTO dbo.People

          (name, sex, age)

  SELECT name,

         sex,

         age

 FROM  Seq

                     OPTION (MAXRECURSION 0)    

  SELECT [语句执行花费时间(毫秒)]=DATEDIFF(ms,@d,GETDATE())

 

  SET STATISTICS IO OFF;

  SET STATISTICS TIME OFF;

  GO

 

使用时间为28430秒

 

 

 

 

4.总结

A:仅仅是while循环运行了614280s,

B:set nocount on 和TRANSACTION运行了267566s,

C:使用了with as 递归插入 运行了28430s。

 

A/B=2.3

A/C=21938

 

使用with as 的性能是最简单循环插入的2万多倍