SQL Server中几种遍历方式比较

SQL遍历解析

　　在SQL的存储过程，函数中，经常需要使用遍历（遍历table），其中游标、临时表等遍历方法很常用。面对小数据量，这几种遍历方法均可行，但是面临大数据量时，就需要择优选择，不同的遍历方法，在效率上存在指数级别的差异

本文列举了常用的3中遍历方法：游标、临时表、索引表，重点分析其效率。

　　本文主要针对大量数据的遍历，当数据量小时；可以随意选择一种遍历方法。

　　实例数据如下：一共177471条数据

　　需求：更新LDBM，其中LDBM=LXBM+LDXLH；即update V_TL_T_LD_ALL set LDBM=LXBM+LDXLH:为了演示遍历，我们循环遍历，一条条数据更新，同时统计其效率，并初略分析其原因

1.游标遍历

游标的遍历

游标是一种最常用的方法，使用起来比较简单，主要步骤为：声明游标，打开游标，使用游标，关闭游标和释放游标。示例代码如下

 1 -- 方法1：游标
 2 -- 声明变量
 3 DECLARE
 4     @LDBM AS NVARCHAR(20),
 5     @LDXLH AS NVARCHAR(20),
 6     @LXBM AS NVARCHAR(20),
 7     @CROWID AS NVARCHAR(80);
 8     
 9 -- 声明游标
10 DECLARE T_LD CURSOR FAST_FORWARD FOR
11     SELECT LDBM,LDXLH,LXBM,CROWID
12     FROM V_TL_LD_ALL1
13     
14 OPEN T_LD;
15 
16 -- 取第一条记录
17 FETCH NEXT FROM T_LD INTO @LDBM,@LDXLH,@LXBM,@CROWID;
18 
19 WHILE @@FETCH_STATUS=0
20 BEGIN
21     -- 操作
22     UPDATE V_TL_LD_ALL1 SET LDBM= @LDBM+' '+@LDXLH WHERE CROWID=@CROWID;
23     
24     -- 取下一条记录
25     FETCH NEXT FROM T_LD INTO @LDBM,@LDXLH,@LXBM,@CROWID;
26 END
27 
28 -- 关闭游标
29 CLOSE T_LD;
30 
31 -- 释放游标
32 DEALLOCATE T_LD;

游标是最直接的从表里面一条条的数据取出，并进行update操作，没有涉及到索引，如果数据量大，其取数据和update都将消耗大量的时间，因此此种方式效率很低。

从对数据库的操作上，其一共操作数据库2n+1次，将数据取出并存入游标（申明游标）：1次；update更新操作：n次；从游标取记录：n次；将数据存储到游标和消耗了大量的内存，且随着数据量的增大，消耗值将呈现指数增加

更新上述177474条数据一共消耗了2h48min37s

2.临时表

使用游标不仅仅存在性能的问题，也违背面向集合思想的问题，所以我们有必要用面向集合的思想去找到一种更好的解决方案，即使用面向对象的思想，构造一个临时表，然后直接操作临时表，代码如下。

 1 -- 方法2：使用临时表
 2 -- 创建临时表
 3 SELECT LDBM,LDXLH,LXBM,CROWID
 4 INTO #T_LD
 5 FROM V_TL_LD_ALL1
 6 
 7 -- 声明变量
 8 DECLARE
 9       @LDBM AS NVARCHAR(20),
10       @LDXLH AS NVARCHAR(20),
11       @LXBM AS NVARCHAR(20),
12       @CROWID AS NVARCHAR(80);
13     
14 WHILE EXISTS(SELECT CROWID FROM #T_LD)
15 BEGIN
16     -- 也可以使用top 1
17     SET ROWCOUNT 1
18     SELECT @LXBM= LXBM, @LDXLH= LDXLH,@CROWID=CROWID FROM #T_LD;
19     UPDATE V_TL_LD_ALL1 SET LDBM= @LXBM+' '+@LDXLH WHERE CROWID=@CROWID;
20     SET ROWCOUNT 0
21     
22     DELETE FROM #T_LD WHERE CROWID=@CROWID;
23 END

 使用临时表，和游标类似，同时将大量的数据存储到内存中，但是随着遍历的进行，临时表的数据量越来越小，可以相当程度的降低内存的消耗，但是需要不停的与table表做交互，一共操作3n+1次数据库；

此种方式，更新上述数据一共消耗：1H:45min:37S

3.索引表

索引表和临时表的操作类似；唯一区别在于在建立临时表是，添加一个索引，然后通过此索引从表中取数据；效率上有所提升，但是增加了变量的输出，代码如下

 1 --方法3：使用索引表
 2 --创建临时表
 3 IF EXISTS(Select Name From Sysobjects Where Name='tmpTable')  
 4 DROP table tmpTable     --存在则删除  
 5 create table tmpTable(
 6     NID int primary key identity(1,1),               --主键，自增
 7     CrowId nvarchar(90), 
 8     LXBM nvarchar(20),
 9     LDXLH nvarchar(6),                        
10 )  
11 --插入数据
12 insert into tmpTable(CrowId,LXBM,LDXLH)
13 select CrowId,LXBM,LDXLH from V_TL_LD_ALL1
14 
15 -- 声明变量
16 DECLARE
17 @index int,
18 @countNum int,
19 @LDBM AS NVARCHAR(20),
20 @LDXLH AS NVARCHAR(20),
21 @LXBM AS NVARCHAR(20),
22 @CROWID AS NVARCHAR(80);
23 
24 select @countNum=count(1) from  tmpTable;
25 set @index=0;
26 --遍历
27 while @index<@countNum
28     begin
29         set @index=@index+1;
30         select @LXBM= LXBM, @LDXLH= LDXLH,@CROWID=CROWID from tmpTable where NID=@index
31         UPDATE V_TL_LD_ALL1 SET LDBM= @LXBM+' '+@LDXLH WHERE CROWID=@CROWID;
32     end
33 
34 --删除临时索引表
35 DROP table tmpTable  

临时索引表和临时表类似，区别在于：在取数据的时候，通过索引的方式取数据；相比临时表，减少了频繁操作数据库的次数，相比游标，减少了与数据库交互的时间（索引检索速度更快）

更新上述数据，一共消耗：1H2,min

 

比较而言，建议尽可能少的使用游标，不仅消耗内存，代码量也稍微复杂一些；当数据量小的时候，建议使用临时表（代码比较轻量），而随着数据的增加，建议使用索引表

 

转载于:https://www.cnblogs.com/dz-boss/p/8990748.html