SQL Server中几种遍历方式比较

时间:2022-02-17 14:17:39

SQL遍历解析

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

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

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

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

SQL Server中几种遍历方式比较

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

1.游标遍历

游标的遍历

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

 -- 方法1:游标
-- 声明变量
DECLARE
@LDBM AS NVARCHAR(20),
@LDXLH AS NVARCHAR(20),
@LXBM AS NVARCHAR(20),
@CROWID AS NVARCHAR(80); -- 声明游标
DECLARE T_LD CURSOR FAST_FORWARD FOR
SELECT LDBM,LDXLH,LXBM,CROWID
FROM V_TL_LD_ALL1 OPEN T_LD; -- 取第一条记录
FETCH NEXT FROM T_LD INTO @LDBM,@LDXLH,@LXBM,@CROWID; WHILE @@FETCH_STATUS=0
BEGIN
-- 操作
UPDATE V_TL_LD_ALL1 SET LDBM= @LDBM+' '+@LDXLH WHERE CROWID=@CROWID; -- 取下一条记录
FETCH NEXT FROM T_LD INTO @LDBM,@LDXLH,@LXBM,@CROWID;
END -- 关闭游标
CLOSE T_LD; -- 释放游标
DEALLOCATE T_LD;

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

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

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

2.临时表

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

 -- 方法2:使用临时表
-- 创建临时表
SELECT LDBM,LDXLH,LXBM,CROWID
INTO #T_LD
FROM V_TL_LD_ALL1 -- 声明变量
DECLARE
@LDBM AS NVARCHAR(20),
@LDXLH AS NVARCHAR(20),
@LXBM AS NVARCHAR(20),
@CROWID AS NVARCHAR(80); WHILE EXISTS(SELECT CROWID FROM #T_LD)
BEGIN
-- 也可以使用top 1
SET ROWCOUNT 1
SELECT @LXBM= LXBM, @LDXLH= LDXLH,@CROWID=CROWID FROM #T_LD;
UPDATE V_TL_LD_ALL1 SET LDBM= @LXBM+' '+@LDXLH WHERE CROWID=@CROWID;
SET ROWCOUNT 0 DELETE FROM #T_LD WHERE CROWID=@CROWID;
END

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

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

3.索引表

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

 --方法3:使用索引表
--创建临时表
IF EXISTS(Select Name From Sysobjects Where Name='tmpTable')
DROP table tmpTable --存在则删除
create table tmpTable(
NID int primary key identity(1,1), --主键,自增
CrowId nvarchar(90),
LXBM nvarchar(20),
LDXLH nvarchar(6),
)
--插入数据
insert into tmpTable(CrowId,LXBM,LDXLH)
select CrowId,LXBM,LDXLH from V_TL_LD_ALL1 -- 声明变量
DECLARE
@index int,
@countNum int,
@LDBM AS NVARCHAR(20),
@LDXLH AS NVARCHAR(20),
@LXBM AS NVARCHAR(20),
@CROWID AS NVARCHAR(80); select @countNum=count(1) from tmpTable;
set @index=0;
--遍历
while @index<@countNum
begin
set @index=@index+1;
select @LXBM= LXBM, @LDXLH= LDXLH,@CROWID=CROWID from tmpTable where NID=@index
UPDATE V_TL_LD_ALL1 SET LDBM= @LXBM+' '+@LDXLH WHERE CROWID=@CROWID;
end --删除临时索引表
DROP table tmpTable

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

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

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