【个人学习笔记13之--游标薄见】

浅谈 游标.
这个东西很难说,存在必有其存在的道理.不能一棍子说它怎么不好,效率多差,它还是有适合用的地方,不然微软也不会把它做出来让大家用.这
本身就说明他是个不可舍去的东西..根据书上,我说说游标的使用

 

1.游标为什么这么受'弃用'

 a.游标和关系模式的主要前提是冲突的。关系模式是一个集合的逻辑,它注重的你在执行代码后获取什么’。
  但是游标是一种过程的逻辑,它注重的是'如何处理数据'.

 b.这个游标逐行操作产生的行操作开销是比较巨大的，一般他的速度也比集合思想处理的慢.(但是有些情况下的数据分布也可能决定游标要快)

 c.这个游标要强制我们的优化器执行固定的计划.不是像集合的解决方法那样选一个比较优秀的执行计划. 

 
   举个例子说明游标的开销: 我们就做扫描表的操作.实验成 先把SSMS的执行后放弃结果选项后再执行代码。
   这样可以保证屏蔽生成输出浪费的时间,保证我们的2个操作时间差就差在性能上,体现游标的开销

---1.先插入1000000条数据进入T1表,(这里的NUMS表是一个辅助表,我前面的学习笔记写过很多方法创建这个临时表) SET NOCOUNT ON; USE tempdb; GO IF OBJECT_ID('dbo.T1') IS NOT NULL DROP TABLE dbo.T1; GO SELECT n AS keycol, CAST('a' AS CHAR(200)) AS filler INTO dbo.T1 FROM dbo.Nums; ---2.在字段上建立索引 CREATE UNIQUE CLUSTERED INDEX idx_keycol ON dbo.T1(keycol) ---3.清空缓存 DBCC DROPCLEANBUFFERS; GO ---4.执行这条记录2次，一定要选中放弃执行结果选项(工具|选项|查询结果|SQL SERVER|以网格或者文本显示结果)里有这个选项 --第一次运行把数据加载至内存,书上叫冷缓存,第二次叫热缓存 SELECT keycol, filler FROM dbo.T1;    

 

----我这机器第一次是7秒,第二次是1秒

  ---5.再次清空缓存 DBCC DROPCLEANBUFFERS; GO ---6.运行如下游标2次 DECLARE @keycol AS INT, @filler AS CHAR(200); DECLARE C CURSOR FAST_FORWARD FOR SELECT keycol, filler FROM dbo.T1; OPEN C FETCH NEXT FROM C INTO @keycol, @filler; WHILE @@fetch_status = 0 BEGIN FETCH NEXT FROM C INTO @keycol, @filler; END CLOSE C; DEALLOCATE C; GO   

 

  ----我的机器上一次22秒,一次17秒.

 

   ------我们分析结果 只考虑热缓存,第二次没写入内存 这样就不涉及物理的I/O操作,你自己也看到了 我这赤裸裸的17倍速度.(每个人机器上不一样)
  ---但是可以肯定 集合的比游标要快很多倍.
  
  PS；这里还有个三不像的解法.看起来像游标,其实基于集合.（一定要选中放弃执行结果选项）
DECLARE @keycol AS INT, @filler AS CHAR(200); SELECT @keycol = keycol, @filler = filler FROM (SELECT TOP (1) keycol, filler FROM dbo.T1 ORDER BY keycol) AS D; WHILE @@rowcount = 1 BEGIN SELECT @keycol = keycol, @filler = filler FROM (SELECT TOP (1) keycol, filler FROM dbo.T1 WHERE keycol > @keycol ORDER BY keycol) AS D; END GO  
  ---这个方法在我机器上第一次运行了20秒,第二次是14秒(- - || 我很郁闷 ,书上说要比游标还慢好几倍..可是测试来测试就是差不多嘛)
  
 d.我们这个游标的代码还不是很长,这还看不出游标的代码还需要维护的成本,但是往往游标非常长的,那是需要成本的.
 
 
2.游标也有受'亲赖'的时候
 当问题本身就是基于顺序的时候,游标是很有可能比基于集合的代码更加快(但不是一定快)

 

 

 a.自定义的一些聚合

 

--首先插入数据
 USE tempdb; GO IF OBJECT_ID('dbo.Groups') IS NOT NULL DROP TABLE dbo.Groups; GO CREATE TABLE dbo.Groups ( groupid VARCHAR(10) NOT NULL, memberid INT NOT NULL, string VARCHAR(10) NOT NULL, val INT NOT NULL, PRIMARY KEY (groupid, memberid) ); INSERT INTO dbo.Groups(groupid, memberid, string, val) VALUES('a', 3, 'stra1', 6); INSERT INTO dbo.Groups(groupid, memberid, string, val) VALUES('a', 9, 'stra2', 7); INSERT INTO dbo.Groups(groupid, memberid, string, val) VALUES('b', 2, 'strb1', 3); INSERT INTO dbo.Groups(groupid, memberid, string, val) VALUES('b', 4, 'strb2', 7); INSERT INTO dbo.Groups(groupid, memberid, string, val) VALUES('b', 5, 'strb3', 3); INSERT INTO dbo.Groups(groupid, memberid, string, val) VALUES('b', 9, 'strb4', 11); INSERT INTO dbo.Groups(groupid, memberid, string, val) VALUES('c', 3, 'strc1', 8); INSERT INTO dbo.Groups(groupid, memberid, string, val) VALUES('c', 7, 'strc2', 10); INSERT INTO dbo.Groups(groupid, memberid, string, val) VALUES('c', 9, 'strc3', 12); GO  

 

 --方法1：游标
DECLARE @Result TABLE(groupid VARCHAR(10), product BIGINT); DECLARE @groupid AS VARCHAR(10), @prvgroupid AS VARCHAR(10), @val AS INT, @product AS BIGINT; DECLARE C CURSOR FAST_FORWARD FOR SELECT groupid,val FROM dbo.Groups ORDER BY groupid; OPEN C FETCH NEXT FROM C INTO @groupid, @val; SELECT @prvgroupid = @groupid, @product = 1; WHILE @@fetch_status = 0 BEGIN IF @groupid <> @prvgroupid BEGIN INSERT INTO @Result VALUES(@prvgroupid, @product); SELECT @prvgroupid = @groupid, @product = 1; END SET @product = @product * @val; FETCH NEXT FROM C INTO @groupid, @val; END IF @prvgroupid IS NOT NULL INSERT INTO @Result VALUES(@prvgroupid, @product); CLOSE C; DEALLOCATE C; SELECT groupid, product FROM @Result; GO 

 --方法2：PIVOTING 聚合
 SELECT groupid, MAX(CASE WHEN rn = 1 THEN val ELSE 1 END) * MAX(CASE WHEN rn = 2 THEN val ELSE 1 END) * MAX(CASE WHEN rn = 3 THEN val ELSE 1 END) * MAX(CASE WHEN rn = 4 THEN val ELSE 1 END) AS product FROM (SELECT groupid, val, (SELECT COUNT(*) FROM dbo.Groups AS B WHERE B.groupid = A.groupid AND B.memberid <= A.memberid) AS rn FROM dbo.Groups AS A) AS D GROUP BY groupid; --方法3：特殊方法 SELECT groupid, POWER(10., SUM(LOG10(val))) AS product FROM dbo.Groups GROUP BY groupid;  

 

 ------------结果----------------
/* groupid product ---------- --------------------------------------- a 42 b 693 c 960 */  

 ----上面三种方法，用游标虽然不是很快,但是它简单 通用 ,第二种方法需要组中的元素比较少,第三种方法奇快无比,但是它只适合用来算正数
 ---所以这里使用游标还是不错的.       
 
 b.连续聚合(累计组内从第一个元素到当前元素)

 --插入数据

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); GO set nocount on   

 --方法1：游标
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; GO   

 --方法2：集合的思想
SELECT O1.empid, CONVERT(VARCHAR(7), O1.ordmonth, 121) AS ordmonth, O1.qty AS qtythismonth, (SELECT SUM(O2.qty) FROM dbo.EmpOrders AS O2 WHERE O2.empid = O1.empid AND O2.ordmonth <= O1.ordmonth) AS totalqty FROM dbo.EmpOrders AS O1 GROUP BY O1.empid, O1.ordmonth, O1.qty;  

  ---基于游标的解决方案呢 只扫描表一次.方案性能成线性变化.基于集合的却是基于 N的平方 变化.
  --一般如果有g组,一组里有N行,那么你将扫描 g*(n+n*n)/2行.这里假设列上有索引的情况下.如果你的组足够小,那么使用基于集合的方案
  --要比游标快很多.随着组的增大,性能集合方案性能将以N*N的速度下降.而基于游标的解决方法性能是一条直线.
   --从书上一个实验图看出结果:随着行数的增加,基于游标的方案性能的优势将越来越明显.因为一个是N*N的抛物线,一个是直线.
  
   PS:当SQL支持 sum() over (partition by XX ORDER BY xxx)的时候 集合的这个方案可能比游标更加快
  
 c.还有一些匹配方面的问题 游标也比较快.因为比较难,自己也没理解透彻.
 
                      说实话 我也不喜欢游标 但是它确实存在着 并且有它一定的用武之地目前.