时间:2021-07-01 10:21:17 帮助过:21人阅读
执行后结果:
那么假如我们要批量插入10000条数据,应该怎么办?
这里有四种方法(普通循环,事务循环、批量插入、cte插入)
1、普通循环插入(while)
/******************************************* ***普通循环(插入数据10000,执行时间:1283毫秒) ********************************************/ --开启开关(记录sql语句各个阶段所消耗的时间)--- set statistics time on; --声明两个变量--- declare @Index int; declare @Timer datetime; --对两个变量进行赋值---- set @Index = 1; set @Timer = GETDATE(); --当循环小于1000次执行添加语句--- while @Index <=10000 begin --执行添加的语句-- insert into Employee(EmployeeNo,EmployeeName,CreateUser,CreateDate) values(@Index,‘员工‘+ cast(@Index as CHAR(5)),‘system‘,GETDATE()) --设置循环次数加1 set @Index = @Index+1 end --获取执行的毫秒数-- select DATEDIFF(MS,@Timer,GETDATE()) as ‘执行时间(毫秒)‘ --关闭开关(记录SQL语句各阶段所消耗的时间) set statistics time off;
执行普通循环插入10000条数据,大概需要1200多毫秒,结果如图所示
2、事务循环插入
/******************************************* ***事务循环(插入数据1000,执行时间:460毫秒) ********************************************/ --开启事务-- begin tran; --开启开关(记录sql语句各个阶段所消耗的时间)--- set statistics time on; --声明两个变量--- declare @Index int; declare @Timer Datetime; --对两个变量进行赋值---- set @Index=1; set @Timer = GETDATE(); --当循环小于1000次执行添加语句--- while @Index <=10000 begin --执行添加的语句-- insert into Employee(EmployeeNo,EmployeeName,CreateUser,CreateDate) values(@Index,‘员工‘+ cast(@Index as CHAR(5)),‘system‘,GETDATE()) --设置循环次数加1 set @Index = @Index+1 end --获取执行的毫秒数-- select DATEDIFF(MS,@Timer,GETDATE()) as ‘执行时间(毫秒)‘ set statistics time off; --提交事务-- commit;
执行事务循环插入10000条数据,大概需要400多毫秒,结果如下所示:
3、批量插入
/******************************************* ***批量插入(插入数据10000,执行时间:33毫秒) ********************************************/ --开启开关(记录sql语句各个阶段所消耗的时间)-- set statistics time on; --声明一个时间变量--- declare @Timer datetime; ---对时间变量进行赋值--- set @Timer = GETDATE(); ---执行批量操作的sql语句--- insert Employee(EmployeeNo,EmployeeName,CreateUser,CreateDate) select top(10000) EmployeeNo=ROW_NUMBER() over( order by c1.[object_id]),‘员工‘,‘system‘,GETDATE() from sys.columns as c1 cross join sys.columns as c2 order by c1.object_id --获取执行的毫秒数-- select DATEDIFF(MS, @Timer, GETDATE()) AS [执行时间(毫秒)]; --关闭开关(记录SQL语句各阶段所消耗的时间)-- SET STATISTICS TIME OFF;
执行批量插入10000条数据,大概只要33毫秒,结果如图所示:
4、CTE插入
--/******************************************* --***CTE插入(插入数据10000,执行时间:40毫秒) --********************************************/ --开启开关(记录sql语句各个阶段所消耗的时间)-- set statistics time on; --声明一个时间变量并赋值-- declare @Timer datetime = GETDATE(); ---将要添加10000条语句组合成CTE模块--- ;with CTE(EmployeeNo,EmployeeName,CreateUser,CreateDate) as ( select top(10000) EmployeeNo = ROW_NUMBER() over (order by C1.[OBJECT_ID]), ‘员工‘, ‘system‘, GETDATE() from SYS.COLUMNS as C1 cross join SYS.COLUMNS as C2 order by C1.[OBJECT_ID] ) --执行CTE插入语句--- insert Employee select EmployeeNo,EmployeeName,CreateUser,CreateDate from CTE; --获取执行的毫秒数-- select DATEDIFF(MS, @Timer, GETDATE()) as [执行时间(毫秒)]; ---关闭开关(记录sql语句各个阶段所消耗的时间)--- set statistics time off;
执行插入10000条数据,大概需要40毫秒,结果如图所示:
最后我们查看一下,批量插入10000条数据的员工表
小结:
1)按执行时间,效率依次为:CTE和批量插入效率相当,速度最快,事务插入次之,单循环插入速度最慢;
2)单循环插入速度最慢是由于INSERT每次都有日志,事务插入大大减少了写入日志次数,批量插入只有一次日志,CTE的基础是CLR,善用速度是最快的
那么,假如我们要批量删除我们插入的数据,怎么办呢?
批量删除有3中方法(循环删除、批量删除、truncate 删除)
1、循环删除
--/******************************************* --***循环删除(删除数据10000,执行时间:20毫秒) --********************************************/ set statistics time on; --声明一个时间变量--- declare @Timer datetime = GETDATE(); --删除语句-- delete from Employee --获取执行的毫秒数-- select DATEDIFF(MS, @Timer, GETDATE()) as [执行时间(毫秒)]; set statistics time off;
删除10000条数据,所需的时间大概为20毫秒,如下所示: