SQL2008使用CTE递归查询批量插入500万数据
SQL2008使用CTE递归查询批量插入500万数据
一直都使用一般的批量循环插入方法 ，可这效率也太慢了，插入500万条数据执行所需的时间远远超过10分钟。于是度娘了一下 发现可以使用CTE进行递归循环插入
以下是亲测的数据：
1、创建表：
CREATE TABLE Customers (CustomerID INT primary key identity ( 1 , 1 ), CustomerNumber CHAR ( 4 ), CustomerName VARCHAR ( 50 ), CustomerCity VARCHAR ( 20 )) GO
2、使用一般批量循环处理
/* 使用一般的批量循环插入 */
declare @maxSum int ,
@customernum nvarchar ( 50 ),
@customername nvarchar ( 50 ),
@city nvarchar ( 50 ),
@paytime datetime
set @maxSum = 1
set @customernum = ' 0000 '
set @customername = ' Customer 0 '
set @city = ' X-City '
while @maxSum < 5000000 /* 一般方法循环插入500万条数据 10分钟以上 */
begin
insert into Customers (CustomerNumber,CustomerName,CustomerCity)
values ( @customernum , @customername , @city )
set @maxSum = @maxSum + 1
end
select [ 500万数据量插入完毕，共花费时间(毫秒) ] = datediff (ms, @paytime , getdate ())
结果：（10分钟已过 还没添加完毕...）
3、使用CTE方法
/* 使用CTE递归循环插入 运用CTE递归插入，速度较快 */ TRUNCATE table Customers GO DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE SET STATISTICS IO ON ; SET STATISTICS TIME ON ; GO DECLARE @d Datetime SET @d = getdate (); WITH Seq (num,CustomerNumber, CustomerName, CustomerCity) AS ( SELECT 1 , ' 0000 ' , ' Customer 0 ' , cast ( ' X-City ' as NVARCHAR ( 20 )) UNION ALL SELECT num + 1 , ' 0000 ' , ' Customer 0 ' , cast ( CHAR ( 65 + (num % 26 )) + ' -City ' AS NVARCHAR ( 20 )) FROM Seq WHERE num <= 5000000 /* CTE批量递归500万数据量 用时一般大概3分钟 因个人电脑而已 */ ) INSERT INTO Customers (CustomerNumber, CustomerName, CustomerCity) SELECT CustomerNumber, CustomerName, CustomerCity FROM Seq OPTION (MAXRECURSION 0 ) select [ 500万数据量插入完毕，共花费时间(毫秒) ] = datediff (ms, @d , getdate ()) SET STATISTICS IO OFF ; SET STATISTICS TIME OFF ; GO
结果：（185秒 约3分钟左右）
网上也查了一下 说：CTE其实是面向对象的，运行的基础是CLR。看来递归 CTE 可以极大地简化在 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句中运行递归查询所需的代码。
小记一下 回去再研究研究 下班！
分类: Asp.net
自己动手写个小框架之五
上一篇提到smarty的封装，这篇将介绍 控制类对其封装 的调用。控制类中有多个action函数，每个函数可以对应不同的页面，一个函数中可能根据业务逻辑对应多个页面，对页面数据的绑定也同时在action函数中绑定。我们看看defaultController.php
1 <? php
2
3 class defaultController extends Controller {
4
5 public function indexAction( $parameter ) {
6 $request = new request();
7 // 给session赋值
8 $request ->set("luf&d", "luf&d" );
9 $this ->render('default.tpl', array ("value" => $parameter [0],
10 "Name" => "index Action page",
11 "FirstName" => array ("John", "Mary", "James", "Henry"),
12 "LastName" => array ("Doe", "Smith", "Johnson", "Case"),
13 "contacts" => array ( array ("phone" => "1", "fax" => "2", "cell" => "3"),
14 array ("phone" => "555-4444", "fax" => "555-3333", "cell" => "760-1234" ))));
15 }
16
17 public function newAction( $parameter ) {
18 $this ->render('default1.tpl', array ("value" => $parameter [0],
19 "Name" => "new Action page" ));
20 }
21
22 public function argsAction( $parameter ) {
23 $this ->render('default1.tpl', array ("value" => $parameter [0] . $parameter [1],
24 "Name" => "new Action page" ));
25 }
26
27 public function requestAction( $parameter = NULL ) {
28 $request = new request();
29 // 获取"luf&d"的值
30 $getinfo = $request ->get("luf&d" );
31 $this ->render('default1.tpl', array ("value" => $getinfo ,
32 "Name" => "index Action page",
33 "FirstName" => array ("John", "Mary", "James", "Henry"),
34 "LastName" => array ("Doe", "Smith", "Johnson", "Case"),
35 "contacts" => array ( array ("phone" => "1", "fax" => "2", "cell" => "3"),
36 array ("phone" => "555-4444", "fax" => "555-3333", "cell" => "760-1234" ))));
37 }
38
39 }
40
41 ?>
该类继承Controller类，可以操作父类的Tpl对象来调用smarty的封装方法render,从而触发smarty模板机制。例如indexAction函数
public function indexAction($parameter) 函数
给default.tpl模板赋值：value,Name,FirstName,LastName,contacts。接着，我们看看default.tpl，里面用到smarty官方的例子
tpl模板文件在tpls/templates/ 里
在tpl文件中“<{”与“}>”成对出现，是我们封装smarty时，设置的模板标签起始和结束标志。在标记中根据前端展现需要编写页面逻辑即可。这里显示了赋值显示及一些smarty的内置函数，如显示当前系统时间、获取当前脚本、获取服务器、字符串全大写等；还有标签条件逻辑语法和循环逻辑语法的使用。
<{config_load file ="test.conf" section="setup"}>
<{ include file ="header.tpl" title=foo}>
<PRE>
( *^__^*)<{ $value }>
<{* bold and title are read from the config file *}>
<{ if # bold#}><b><{/if}>
<{* capitalize the first letters of each word of the title *}>
Title : <{ # title#|capitalize}>
<{ if # bold#}></b><{/if}>
The current date and time is <{ $smarty .now|date_format:"%Y-%m-%d %H:%M:%S"}>
The value of global assigned variable $SCRIPT_NAME is <{ $SCRIPT_NAME }>
Example of accessing server environment variable SERVER_NAME : <{ $smarty .server.SERVER_NAME}>
The value of <{ldelim}> $Name <{rdelim}> is <b><{ $Name }></b>
<b><{ $Name |upper}></b>
An example of a section loop :
<{section name= outer
loop = $FirstName }>
<{ if $smarty .section.outer.index is odd by 2}>
<{ $smarty .section.outer.rownum}> . <{ $FirstName [outer]}> <{ $LastName [outer]}>
<{ else }>
<{ $smarty .section.outer.rownum}> * <{ $FirstName [outer]}> <{ $LastName [outer]}>
<{/ if }>
<{sectionelse}>
none
<{/section}>
An example of section looped key values:
<{section name=sec1 loop= $contacts }>
phone : <{ $contacts [sec1].phone}><br>
fax : <{ $contacts [sec1].fax}><br>
cell : <{ $contacts [sec1].cell}><br>
<{/section}>
<p>
<{ include file ="footer.tpl"}>
其中引入header.tpl和footer.tpl这对于整个系统统一界面很重要，加入前端美工设计后，能使整个系统的一致。还引入了test.conf
header.tpl
View Code
footer.tpl
View Code
conf文件在tpls/configs/里
test.conf
View Code
运行效果：
在浏览器中输入路由地址 http://localhost/dluf/index.php/default/index/dluf
在 系列六中 将介绍对页面传值的封装。
标签: php , smarty , 框架
作者： Leo_wl
出处： http://www.cnblogs.com/Leo_wl/
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。
版权信息查看更多关于SQL2008使用CTE递归查询批量插入500万数据的详细内容...