sql server中的大数据的批量操作（批量插入，批量删除）

a13698822086

　　首先我们建立一个测试用员工表
　　

　　
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