一循环插入数据库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万多倍