sql server中的大数据的批量操作（批量插入，批量删除）
首先我们建立一个测试用员工表create table Employee(
EmployeeNo int primary key,
EmployeeName nvarchar(50) null,
CreateUser nvarchar(50) null,
CreateDate datetime null,
)
执行后结果：
那么假如我们要批量插入10000条数据，应该怎么办？
这里有四种方法（普通循环，事务循环、批量插入、cte插入）
1、普通循环插入（while）
set statistics time on;
declare @Index int;
declare @Timer datetime;
set @Index = 1;
set @Timer = GETDATE();
while @Index <=10000
begin
insert into Employee(EmployeeNo,EmployeeName,CreateUser,CreateDate)
values(@Index,'员工'+ cast(@Index as CHAR(5)),'system',GETDATE())
set @Index = @Index+1
end
select DATEDIFF(MS,@Timer,GETDATE()) as '执行时间（毫秒）'
set statistics time off;
执行普通循环插入10000条数据，大概需要1200多毫秒，结果如图所示
2、事务循环插入
begin tran;
set statistics time on;
declare @Index int;
declare @Timer Datetime;
set @Index=1;
set @Timer = GETDATE();
while @Index <=10000
begin
insert into Employee(EmployeeNo,EmployeeName,CreateUser,CreateDate)
values(@Index,'员工'+ cast(@Index as CHAR(5)),'system',GETDATE())
set @Index = @Index+1
end
select DATEDIFF(MS,@Timer,GETDATE()) as '执行时间（毫秒）'
set statistics time off;
commit;
执行事务循环插入10000条数据，大概需要400多毫秒，结果如下所示：
小结：
1）按执行时间，效率依次为：CTE和批量插入效率相当，速度最快，事务插入次之，单循环插入速度最慢；
2）单循环插入速度最慢是由于INSERT每次都有日志，事务插入大大减少了写入日志次数，批量插入只有一次日志，CTE的基础是CLR，善用速度是最快的
那么，假如我们要批量删除我们插入的数据，怎么办呢？
批量删除有3中方法（循环删除、批量删除、truncate 删除）
1、循环删除
set statistics time on;
declare @Timer datetime = GETDATE();
delete from Employee
select DATEDIFF(MS, @Timer, GETDATE()) as [执行时间(毫秒)];
set statistics time off;
删除10000条数据，所需的时间大概为20毫秒，如下所示：
2、批量删除
set statistics time on;
declare @Timer datetime = GETDATE();
SET ROWCOUNT 10000;
while 1 = 1
begin
begin tran
delete from Employee;
commit;
IF @@ROWCOUNT = 0
break;
end
set ROWCOUNT 0;
select DATEDIFF(MS, @Timer, GETDATE()) as [执行时间(毫秒)];
set statistics time off;
删除10000条数据，所需的时间大概为23毫秒，如下所示：
小结：
1）TRUNCATE太快了，清除10W数据一点没压力，批量删除次之，最后的DELTE太慢了
2）TRUNCATE快是因为它属于DDL语句，只会产生极少的日志，普通的DELETE不仅会产生日志，而且会锁记录
PS:
参考学习网址：http://www.cnblogs.com/panchunting/archive/2013/04/27/SQL_Tech_001.html
页:
[1]