[原] 游标替代

[华芸智森]

我一直反对使用游标，游标在SQL定义成面向过程，这与SQL的面向集合感觉有点不符。而且，游标会引起很多性能，资源问题。我前几天查了一下我们的数据库服务器。一共搜出将近一百个用了游标存储过程，更要命的是：游标竟然没有 Close，也没有 DEALLOCATE。俺头大了，俺这个非专职的临时DBA也管不了这么多，全找出来，让他们自行改吧。
俺这临时DBA没有权力，但有些东东，还得龟腚一下。以后严禁使用游标。不让用，得有个替代的办法吧。这个方法别人用了N年，俺也用了N年，CSDN的贴子里也贴了N年，俺再多贴一次，也未不可。

-----------------------
-- 查找未关闭的游标。
-----------------------
CREATE TABLE ##游标 ( Var_DBName VarChar(255),Var_ProcName VarChar(255) );
DECLARE @Var_DbNaem VarChar(255);
DECLARE @NVr_Cmd NVarChar(4000);

SELECT @Var_DbNaem=MIN([NAME]) from master..sysdatabases

WHILE NOT @Var_DbNaem IS NULL BEGIN
SET @NVr_Cmd='
INSERT INTO ##游标(Var_ProcName,Var_DBName)
SELECT distinct 名称,''' +@Var_DbNaem + '''
FROM (
select
b.Name as 名称
,a.Name as 参数
,c.Text as 语句
from
[' +@Var_DbNaem + '].DBO.syscolumns a
left join
[' +@Var_DbNaem + '].DBO.sysobjects b on a.ID=b.ID
join
[' +@Var_DbNaem + '].DBO.syscomments c on c.ID=b.ID
where
b.xtype in(''FN'',''IF'',''TF'',''P'') AND C.TEXT LIKE ''%CURSOR%'' AND (NOT C.TEXT LIKE ''%DEALLOCATE%'')
) AS T'

PRINT @NVR_CMD

EXECUTE(@NVR_CMD);
SELECT @Var_DbNaem=MIN([NAME]) from master..sysdatabases WHERE [NAME]>@Var_DbNaem
END

SELECT * FROM ##游标
DROP TABLE ##游标

GO

USE TEST

GO

CREATE TABLE LX1(
INT_I INT,
VAR_A VARCHAR(32),
VAR_B VARCHAR(32)
)

GO

INSERT INTO LX1 VALUES(1,'A','E')
INSERT INTO LX1 VALUES(2,'B','F')
INSERT INTO LX1 VALUES(3,'C','G')
INSERT INTO LX1 VALUES(4,'D','H')

GO

---------------------------
--原表能确定唯一的单一主键。
--如果是多主键，可以在表变量中添加自增字段（IDENTITY）或字符型的组合字段（ KEY1 + '|' + KEY2 + '| + KEY3 ... ) 。
---------------------------
DECLARE @INT_I INT
DECLARE @VAR_A VARCHAR(32)
DECLARE @VAR_B VARCHAR(32)

DECLARE @TAB_LX TABLE (INT_I INT,VAR_A VARCHAR(32),VAR_B VARCHAR(32))
INSERT INTO @TAB_LX SELECT * FROM LX1
SELECT @INT_I=MIN(INT_I) FROM @TAB_LX
WHILE NOT @INT_I IS NULL BEGIN
	SELECT @VAR_A=VAR_A,@VAR_B=VAR_B FROM @TAB_LX WHERE INT_I=@INT_I
	
	--提取值
	PRINT @VAR_A + '   '  + @VAR_B
	
	SELECT @INT_I=MIN(INT_I) FROM LX1 WHERE INT_I>@INT_I
END

GO

---------------------------
--原表没有唯一主键
--可在表变量中添加一个自增编号。
---------------------------
DECLARE @INT_ID INT
DECLARE @VAR_A VARCHAR(32)
DECLARE @VAR_B VARCHAR(32)

DECLARE @TAB_LX TABLE (INT_ID INT IDENTITY(1,1) ,INT_I INT,VAR_A VARCHAR(32),VAR_B VARCHAR(32))

INSERT INTO @TAB_LX (INT_I,VAR_A,VAR_B) SELECT * FROM LX1
SELECT @INT_ID=MIN(INT_ID) FROM @TAB_LX
WHILE NOT @INT_ID IS NULL BEGIN
	SELECT @VAR_A=VAR_A,@VAR_B=VAR_B FROM @TAB_LX WHERE INT_ID=@INT_ID
	
	---提取值。
	PRINT @VAR_A + '   '  + @VAR_B
	
	SELECT @INT_ID=MIN(INT_ID) FROM @TAB_LX WHERE INT_ID>@INT_ID
END

GO

[zl_20090320]

mark

[Roc_Lee]

因为工作需要操作那些库都是大量访问的库。
我一般就建个临时表。把需要的数据提取出来，反正需要关联。
然后进行循环操作。即不会导致数据库不可用。也可以快速完成操作。

[bhtfg538]

游标的使用其实是不符合关系数据库对象的，因为关系数据库中都是基于（set-based）集合处理的，所以我们不能像在过程语言一样的逐行操作每条记录，但是在某些情况下使用游标会比使用集合处理获得更好的效果，你也要必须了解游标开销是很大的，多数情况下性能的损耗来自创建游标本身，而且游标不像SELECT 处理集合能够使用高效的执行计划，因为他是逐行操作的。我们在以下几种情况下使用游标：
 需要对每行中的部门列进行处理
 自定义聚合
 汇总聚合
 匹配问题
 特定的逻辑处理
在我们决定使用游标的时候，请先对整个时间复杂度进行一个分析，已判断是否符合游标操作。我演示几个方案：
方案一：
USE tempdb;
GO

IF OBJECT_ID('dbo.EmpOrders') IS NOT NULL
DROP TABLE dbo.EmpOrders;
GO

CREATE TABLE dbo.EmpOrders
(
empid INT NOT NULL,
ordmonth DATETIME NOT NULL,
qty INT NOT NULL,
PRIMARY KEY(empid, ordmonth)
);

INSERT INTO dbo.EmpOrders(empid, ordmonth, qty)
SELECT O.EmployeeID,
CAST(CONVERT(CHAR(6), O.OrderDate, 112) + '01'
AS DATETIME) AS ordmonth,
SUM(Quantity) AS qty
FROM Northwind.dbo.Orders AS O
JOIN Northwind.dbo.[Order Details] AS OD
ON O.OrderID = OD.OrderID
GROUP BY EmployeeID,
CAST(CONVERT(CHAR(6), O.OrderDate, 112) + '01'
AS DATETIME);

查询EmpOrders的（部分）结果为
empid ordmonth qty
----------- ----------------------- -----------
1 1998-01-01 00:00:00.000 397
1 1998-02-01 00:00:00.000 566
1 1998-03-01 00:00:00.000 467
1 1998-04-01 00:00:00.000 586
1 1998-05-01 00:00:00.000 299
2 1996-07-01 00:00:00.000 50
2 1996-08-01 00:00:00.000 94
我现在想做如下聚合，对于都是1的订单进行类似这样的汇总
empid ordmonth qty runqty
----------- -------- ----------- -----------
1 1998-04 586 7513
1 1998-05 299 7812
2 日期 当前记录 记录的汇总
意思就是为同一个empid 的qty 进行 叠加，比方如果第一条记录的qty 是1，第2条记录的empid和上一条是一样的，那么对记录进行叠加（第2条记录的qty是2），那么runqty显示3。

如果我们使用基于集合的处理方式，那么我们要利用一个子查询对所有小于当前记录，但是是同一个empid的记录进行SUM汇总，这样的方式时间复杂度就是O(N^2)，因为每次都对上一行的记录进行了SUM操作。换个思维，我们的C#程序员可能就觉得问题很简单了，只要逐行的对每条记录进行“加“操作就能得到正确的结果。而且时间复杂度就是遍历一次记录的操作，当然我们首先要对记录进行排序，所以时间复杂度就是0(2N)。我们就在这种情况下使用游标。
解决方案如下：
DECLARE @Result
TABLE(empid INT, ordmonth DATETIME, qty INT, runqty INT);
DECLARE
@empid AS INT,@prvempid AS INT, @ordmonth DATETIME,
@qty AS INT, @runqty AS INT;

DECLARE C CURSOR FAST_FORWARD FOR
SELECT empid, ordmonth, qty
FROM dbo.EmpOrders
ORDER BY empid, ordmonth;

OPEN C

FETCH NEXT FROM C INTO @empid, @ordmonth, @qty;
SELECT @prvempid = @empid, @runqty = 0;

WHILE @@fetch_status = 0
BEGIN
IF @empid <> @prvempid
SELECT @prvempid = @empid, @runqty = 0;

SET @runqty = @runqty + @qty;

INSERT INTO @Result VALUES(@empid, @ordmonth, @qty, @runqty);

FETCH NEXT FROM C INTO @empid, @ordmonth, @qty;
END

CLOSE C;

DEALLOCATE C;

SELECT empid, CONVERT(VARCHAR(7), ordmonth, 121) AS ordmonth,
qty, runqty
FROM @Result
ORDER BY empid, ordmonth;
当然在实际的数据处理中，还有很多情况可以使用游标。因此在使用的时候，请谨慎考虑。

[bhtfg538]

还有一些问题是可以使用 游标的 。
比如处理 会话的 最大 并发统计


USE tempdb;
GO
IF OBJECT_ID('dbo.Sessions') IS NOT NULL
DROP TABLE dbo.Sessions;
GO

CREATE TABLE dbo.Sessions
(
keycol INT NOT NULL IDENTITY PRIMARY KEY,
app VARCHAR(10) NOT NULL,
usr VARCHAR(10) NOT NULL,
host VARCHAR(10) NOT NULL,
starttime DATETIME NOT NULL,
endtime DATETIME NOT NULL,
CHECK(endtime > starttime)
);

INSERT INTO dbo.Sessions
VALUES('app1', 'user1', 'host1', '20030212 08:30', '20030212 10:30');
INSERT INTO dbo.Sessions
VALUES('app1', 'user2', 'host1', '20030212 08:30', '20030212 08:45');
INSERT INTO dbo.Sessions
VALUES('app1', 'user3', 'host2', '20030212 09:00', '20030212 09:30');

INSERT INTO dbo.Sessions
VALUES('app1', 'user4', 'host2', '20030212 09:15', '20030212 10:30');
INSERT INTO dbo.Sessions
VALUES('app1', 'user5', 'host3', '20030212 09:15', '20030212 09:30');
INSERT INTO dbo.Sessions
VALUES('app1', 'user6', 'host3', '20030212 10:30', '20030212 14:30');
INSERT INTO dbo.Sessions
VALUES('app1', 'user7', 'host4', '20030212 10:45', '20030212 11:30');
INSERT INTO dbo.Sessions
VALUES('app1', 'user8', 'host4', '20030212 11:00', '20030212 12:30');
INSERT INTO dbo.Sessions
VALUES('app2', 'user8', 'host1', '20030212 08:30', '20030212 08:45');
INSERT INTO dbo.Sessions
VALUES('app2', 'user7', 'host1', '20030212 09:00', '20030212 09:30');
INSERT INTO dbo.Sessions
VALUES('app2', 'user6', 'host2', '20030212 11:45', '20030212 12:00');
INSERT INTO dbo.Sessions
VALUES('app2', 'user5', 'host2', '20030212 12:30', '20030212 14:00');
INSERT INTO dbo.Sessions
VALUES('app2', 'user4', 'host3', '20030212 12:45', '20030212 13:30');
INSERT INTO dbo.Sessions
VALUES('app2', 'user3', 'host3', '20030212 13:00', '20030212 14:00');
INSERT INTO dbo.Sessions
VALUES('app2', 'user2', 'host4', '20030212 14:00', '20030212 16:30');
INSERT INTO dbo.Sessions
VALUES('app2', 'user1', 'host4', '20030212 15:30', '20030212 17:00');

CREATE INDEX idx_app_st_et ON dbo.Sessions(app, starttime, endtime);


我们使用 一般的 集合 处理：
SELECT app, MAX(concurrent) AS mx
FROM (SELECT app,
(SELECT COUNT(*)
FROM dbo.Sessions AS S2
WHERE S1.app = S2.app
AND S1.ts >= S2.starttime
AND S1.ts < S2.endtime) AS concurrent
FROM (SELECT DISTINCT app, starttime AS ts
FROM dbo.Sessions) AS S1) AS C
GROUP BY app;



我们使用 游标 大家可以 对比性能：
DECLARE
@app AS VARCHAR(10), @prevapp AS VARCHAR (10), @ts AS datetime,
@event_type AS INT, @concurrent AS INT, @mx AS INT;

DECLARE @Result TABLE(app VARCHAR(10), mx INT);

DECLARE C CURSOR FAST_FORWARD FOR
SELECT app, starttime AS ts, 1 AS event_type FROM dbo.Sessions
UNION ALL
SELECT app, endtime, -1 FROM dbo.Sessions
ORDER BY app, ts, event_type;

OPEN C;

FETCH NEXT FROM C INTO @app, @ts, @event_type;
SELECT @prevapp = @app, @concurrent = 0, @mx = 0;

WHILE @@fetch_status = 0
BEGIN
IF @app <> @prevapp
BEGIN
INSERT INTO @Result VALUES(@prevapp, @mx);
SELECT @prevapp = @app, @concurrent = 0, @mx = 0;
END

SET @concurrent = @concurrent + @event_type;
IF @concurrent > @mx SET @mx = @concurrent;

FETCH NEXT FROM C INTO @app, @ts, @event_type;
END

IF @prevapp IS NOT NULL
INSERT INTO @Result VALUES(@prevapp, @mx);

CLOSE C

DEALLOCATE C

SELECT * FROM @Result;

[bhtfg538]

当然还有一些 逐行处理 和 匹配问题，关闭 匹配问题的 算法 可以去 insidetsql.com 看下

[w20011025]

luguo, 学习

[whitechololate]

先留名再细学。谢分享

[Reasoncool]

高人 学习了

[Reasoncool]

楼主的方法 我测试过 7061行数据 12秒

用游标 0秒

难道我错了？

[yanleiyigan]

lg

[华芸智森]

[Quote=引用 79 楼 Reasoncool 的回复:]
楼主的方法 我测试过 7061行数据 12秒

用游标 0秒

难道我错了？
[/Quote]
注意使用索引。

[华芸智森]

[Quote=引用 82 楼 allanli 的回复:]
大家都说游标哪不好那不好,但是有没有谁能给出一个实测的例子说明
使用临时表或者表变量到底能够提升多少性能呢?

我就尝试把我系统的游标改为使用临时表或者表变量,根本没有发现
性能提升多少,有些地方反而变得性能更差了(因为如果不使用游标,必须增加很多代码量)
[/Quote]
1.代码量与性能没有必然的联系。
2.这里只是举了个例子,实际应用中一般将要处理的数据放到一个表变量中,再在表变量中循环.
3.这种做法在单机的环境下没有性能优势.在多用户的大量并发下性能的提升非常明显.

[allanli]

大家都说游标哪不好那不好,但是有没有谁能给出一个实测的例子说明
使用临时表或者表变量到底能够提升多少性能呢?

我就尝试把我系统的游标改为使用临时表或者表变量,根本没有发现
性能提升多少,有些地方反而变得性能更差了(因为如果不使用游标,必须增加很多代码量)

[zzxap]

顶老乡

[allanli]

如果连单个用户自己测试都没有发现性能提升(我是循环多次测试的),反而性能降低了
那么多用户并发反而能提升性能我就不太明白了,呵呵

不过我也不太提倡使用游标的,游标的创建的确很花时间(我监视我的服务器基本上执行时间长的语句大部分都是游标的创建)
不过不使用游标嘛,的确需要增加很多代码,创建临时表啊,增加自增量字段等(不要告诉我这些语句不用花费时间)

[gw0701]

好长啊。。。

[olddown]

[Quote=引用 16 楼 MSTOP 的回复:]
引用 12 楼 jinjazz 的回复:
盖章，持反对观点的请深入讨论。

我也很少用游标，用表变量可以排除并发问题，但性能不一定高吧。有没有测试数据？

如果是短小精的数据表的话，游标完全没有问题。
但如果一个表中有上十几万或至几百万时，游标的问题就出来了。
现在数据库经常被死锁。最多时多达12个。服务器基本上是完了。有一段时间为了查找锁的问题。我一直在想，到底哪里出了问题。直到前几天我给他们优化一个过程，我…
[/Quote]
说得好

[ghost0912]

学习

[qiuming0306]

看不到翻页的按钮？？

[aoin2006]

顶 ..