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

footer.tpl

conf文件在tpls/configs/里

test.conf

 

运行效果：

在浏览器中输入路由地址http://localhost/dluf/index.php/default/index/dluf

    在系列六中将介绍对页面传值的封装。

 

 

 

标签: php, smarty, 框架