插入大量数据时,尽量不要使用循环,可以使用CTE,如果要使用循环,也放到一个事务中
这点其实显而易见。SQL Server是隐式事务提交的,所以对于每一个循环中的INSERT,都会作为一个事务提交。这种效率可想而知,但如果将1000条语句放到一个事务中提交,效率无疑会提升不少。
打个比方,去银行存款,是一次存1000效率高,还是存10次100?下面,根据吉日的要求,补个例子,见代码1.
CREATE TABLE dbo.TestInsert ( Number INT PRIMARY KEY ); --循环插入,不给力,我的笔记本45秒 DECLARE @index INT; SET @index = 1; WHILE @index <= 100000 BEGIN INSERT dbo.TestInsert(Number) VALUES( @index); SET @index = @index + 1; END --放到一个事务中循环,略好,但也不是最好,我的笔记本1秒 BEGIN TRAN DECLARE @index INT; SET @index = 1; WHILE @index <= 100000 BEGIN INSERT dbo.TestInsert(Number) VALUES( @index); SET @index = @index + 1; END COMMIT --批量插入,10W行,显示0秒,有兴趣的同学改成100W行进行测试 INSERT dbo.TestInsert(Number) SELECT TOP (100000) rn = ROW_NUMBER() OVER (ORDER BY c1.[object_id]) FROM sys.columns AS c1 CROSS JOIN sys.columns AS c2 CROSS JOIN sys.columns AS c3 ORDER BY c1.[object_id]; --CTE方式,和上面那种方式大同小异,也是批量插入,比如: WITH cte AS( SELECT TOP (100000) rn = ROW_NUMBER() OVER (ORDER BY c1.[object_id]) FROM sys.columns AS c1 CROSS JOIN sys.columns AS c2 CROSS JOIN sys.columns AS c3 ORDER BY c1.[object_id] ) INSERT dbo.TestInsert(Number) SELECT rn FROM cte