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