---创建⼀个测试的员⼯表---
create table Employee(
EmployeeNo int primary key, --员⼯编号
EmployeeName nvarchar(50) null, --员⼯名称
CreateUser nvarchar(50) null, --创建⼈
CreateDate datetime null, --创建时间
)
执⾏后结果：
那么假如我们要批量插⼊10000条数据，应该怎么办？
这⾥有四种⽅法（普通循环，事务循环、批量插⼊、cte插⼊）chenxiao
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()
lumns as c1 cross lumns 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毫秒，如下所⽰：
2、批量删除
/*******************************************
没那么简单 铃声***批量删除(删除数据10000，执⾏时间：23毫秒)
********************************************/
set statistics time on;
declare@Timer datetime=GETDATE();
SET ROWCOUNT10000;
吴倩莲老公
while1=1
begin
光能使者主题曲--开启事务--
begin tran
--执⾏删除--
delete from Employee;
--提交事务--
commit;
周杰伦地表最强演唱会歌单IF@@ROWCOUNT=0
break;
end
set ROWCOUNT0;
--获取执⾏的毫秒数---
select DATEDIFF(MS, @Timer, GETDATE()) as[执⾏时间(毫秒)];
set statistics time off;
删除10000条数据，所需的时间⼤概为23毫秒，如下所⽰：
3、truncate删除
--/*******************************************
--***truncate删除(删除数据10000，执⾏时间：3毫秒)
--********************************************/
set statistics time on;
--声明⼀个时间变量--
declare@Timer datetime=getdate();
--执⾏truncate语句--
truncate table Employee
---获取执⾏的毫秒数---
select DATEDIFF(MS, @Timer, GETDATE()) as[执⾏时间(毫秒)]
set statistics time off
删除10000条数据，所需的时间⼤概为3毫秒，如下所⽰：
⼩结：
1）TRUNCATE太快了，清除10W数据⼀点没压⼒，批量删除次之，最后的DELTE太慢了
2）TRUNCATE快是因为它属于DDL语句，只会产⽣极少的⽇志，普通的DELETE不仅会产⽣⽇志，⽽且会锁记录PS:
发布评论