sql server中的大数据的批量操作(批量插入,批量删除)
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了sql server中的大数据的批量操作(批量插入,批量删除),小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含5434字,纯文字阅读大概需要8分钟。
内容图文
-创建一个测试的员工表--- 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毫秒,如下所示:
<img alt="技术分享" src="http://www.mamicode.com/data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAALYAAABeCAIAAAABhXm8AAAYIklEQVR4nO2de1QTZ97HEdt/7HvU97SeukW29pzdnp7tutbdFtt6W30tWpWyvSlaWy14oa5oRWyRrToIykVuQSVyCYFgJAaRAAkKATGEa7gkRBACJCQBArmRCwnKJXneP2aZDoGEqBEQ53t+//xmnjzPZJ4PM9/8fGZ00g8YYi8nP2huGUSpqKiourq6tbWVQqH09fVpn0EymUyK6UWWk21EyGRybW0tF9NLLFuICIXCXEwvvWwh8iz3F0xzRhgimKYQhgimKYQhgmkK2UJEo9EoXw5pNJqZnojZK1uIyOVywcshuVw+0xMxezUFIh0dHUaj8fHcldFo7OjowBCxoSkQEQqFIyMjYO5qZGREKBR2dnbOdA3TMZLJZBgiDhaMiFgsnunJdYBEIpFYLJ5diIyazF2qwU6FYdIQyQ0PxYbmTstQaIanEwLbghGZeKNRKpUymUwmk3VNkEQiecZ/t3pO6u7unnWIaIzDv2Q0HkqsmzR+vFL3ydG6fxyyjMS8HtvTZjabTSaTxUaTyWQ2mx0AxXhZQ6S1tfX+/fulpaV0Oj0fpby8PPINSnU1x+Ez8eyavYiEFMhjy4yxZcbYe8ZYgtE/0Xgo0Xgo0XjwmvH7OOPe2HGxwb91SkRYLJa7u7tCoUC2KBQKd3 本文系统来源:http://www.cnblogs.com/xielong/p/7054927.html
内容总结
以上是互联网集市为您收集整理的sql server中的大数据的批量操作(批量插入,批量删除)全部内容,希望文章能够帮你解决sql server中的大数据的批量操作(批量插入,批量删除)所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。