首页 > 代码库 > sql server中的大数据的批量操作(批量插入,批量删除)

sql server中的大数据的批量操作(批量插入,批量删除)

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

---创建一个测试的员工表---
create table Employee(
    EmployeeNo int primary key,      --员工编号
    EmployeeName nvarchar(50) null,  --员工名称
    CreateUser nvarchar(50) null,    --创建人
    CreateDate datetime null,        --创建时间
) 

执行后结果:

技术分享

那么假如我们要批量插入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毫秒,如下所示:

技术分享

2、批量删除

/*******************************************
***批量删除(删除数据10000,执行时间:23毫秒)
********************************************/

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毫秒,如下所示:

技术分享

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:

参考学习网址:http://www.cnblogs.com/panchunting/archive/2013/04/27/SQL_Tech_001.html

sql server中的大数据的批量操作(批量插入,批量删除)