SQL Server快进游标

在SQL Server中避免使用光标的“最佳实践”可以追溯到SQL Server 2000和早期版本。SQL 2005中对引擎的重写解决了大多数与光标问题相关的问题,特别是引入了快速前进选项。游标不必比基于集的更糟糕,并且在Oracle PL/SQL(循环)中得到了广泛和成功的使用。

你所指的“普遍接受” 是 有效,但现在已过时和不正确-假定快进游标的行为与广告中的和执行的一样。做一些测试和研究,将您的发现建立在sql2005和更高版本上

虽然在SQL Server 2005中,快进光标确实有一些优化,但它是 不 事实上,它们在性能方面接近于基于集合的查询。很少有情况下不能用基于集合的查询替换光标逻辑。游标的速度总是天生较慢,部分原因是为了填充局部变量,必须不断中断执行。

以下是一些参考文献,如果你研究这个问题,这将只是冰山一角:

http://www.code-magazine.com/Article.aspx?quickid=060113

http://dataeducation.com/re-inventing-the-recursive-cte/

“如果您想要一个比快进更快的光标,那么使用静态光标。他们比快进更快。不是非常快,但可以起到作用。”

不要这么快!据微软称: “通常,当发生这些转换时,光标类型降级为更昂贵的光标类型。通常情况下,一个(快速的)前向游标的性能最高,其次是动态的、键集的,最后是静态的,这通常是性能最低的。”

来自: http://blogs.msdn.com/b/mssqlisv/archive/2006/06/23/644493.aspx

大多数情况下,您可以避免使用光标,但有时这是必要的。

记住快速前进是动态的…前进\只能与静态光标一起使用。

尝试在万圣节问题上使用它,看看会发生什么!!!!

IF OBJECT_ID('Funcionarios') IS NOT NULL
DROP TABLE Funcionarios
GO

CREATE TABLE Funcionarios(ID          Int IDENTITY(1,1) PRIMARY KEY,
                          ContactName Char(7000),
                          Salario     Numeric(18,2));
GO

INSERT INTO Funcionarios(ContactName, Salario) VALUES('Fabiano', 1900)
INSERT INTO Funcionarios(ContactName, Salario) VALUES('Luciano',2050)
INSERT INTO Funcionarios(ContactName, Salario) VALUES('Gilberto', 2070)
INSERT INTO Funcionarios(ContactName, Salario) VALUES('Ivan', 2090)
GO

CREATE NONCLUSTERED INDEX ix_Salario ON Funcionarios(Salario)
GO

-- Halloween problem, will update all rows until then reach 3000 !!!
UPDATE Funcionarios SET Salario = Salario * 1.1
  FROM Funcionarios WITH(index=ix_Salario)
 WHERE Salario < 3000
GO

-- Simulate here with all different CURSOR declarations
-- DYNAMIC update the rows until all of then reach 3000
-- FAST_FORWARD update the rows until all of then reach 3000
-- STATIC update the rows only one time. 

BEGIN TRAN
DECLARE @ID INT
DECLARE TMP_Cursor CURSOR DYNAMIC 
--DECLARE TMP_Cursor CURSOR FAST_FORWARD
--DECLARE TMP_Cursor CURSOR STATIC READ_ONLY FORWARD_ONLY
    FOR SELECT ID 
          FROM Funcionarios WITH(index=ix_Salario)
         WHERE Salario < 3000

OPEN TMP_Cursor

FETCH NEXT FROM TMP_Cursor INTO @ID

WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT * FROM Funcionarios WITH(index=ix_Salario)

  UPDATE Funcionarios SET Salario = Salario * 1.1 
   WHERE ID = @ID

  FETCH NEXT FROM TMP_Cursor INTO @ID
END

CLOSE TMP_Cursor
DEALLOCATE TMP_Cursor

SELECT * FROM Funcionarios

ROLLBACK TRAN
GO

人们之所以避免使用光标,是因为它们通常比简单的while循环更难编写,然而,while循环可能会很昂贵,因为您经常从表中选择临时或其他数据。

使用只读快进的光标,数据保存在内存中,并专门为循环设计。

This article 突出显示平均光标运行速度是while循环的50倍。

这个答案希望合并到目前为止给出的答复。

1)如果可能,使用基于集合的逻辑进行查询,即尝试使用 SELECT , INSERT , UPDATE 或 DELETE 与适当的 FROM 子句或嵌套查询-这些几乎总是更快。

2)如果上述方法不可行,则在SQL Server 2005中+ FAST FORWARD 游标效率高,性能良好,应该优先使用while循环。

要回答Mile最初的问题…

快速前进、只读、静态光标(亲切地称为“消防软管光标”)通常与等效临时表和while循环一样快或更快,因为这样的光标只不过是临时表和while循环,在后台进行了一些优化。

为了补充EricZ.Beard在这条线上所贴的内容,并进一步回答以下问题…

“所有关于不使用光标的讨论真的是关于避免使用光标吗? 当基于集合的方法可用时,以及 可更新的光标等。”

对。除了极少数的例外情况,编写适当的基于集合的代码与大多数游标执行相同的操作所需的时间和代码更少,并且具有使用更少资源的额外好处,通常运行速度比游标或while循环快得多。一般来说,除了某些管理任务外,应该避免使用它们,而应该使用正确编写的基于集合的代码。当然,每个“规则”都有例外,但是,对于光标、循环和其他形式的RBAR,大多数人可以一手计算例外,而不需要使用所有的手指。;-)

还有“隐藏的RBAR”的概念。这是看起来基于集但实际上不是的代码。这种类型的“基于集”代码是某些人接受RBAR方法并说他们“好”的原因。例如,在我的书中,使用带有不等式的聚合(和)相关子查询来构建运行总数的运行总数问题并不是真正的基于设置的。相反,它是类固醇的RBAR,因为对于每一行计算,它必须以n*(n+1)/2的速率反复“接触”其他许多行。这被称为“三角形连接”,至少是完整笛卡尔连接(交叉连接或“方形连接”)的一半。

尽管MS在SQL Server 2005之后对光标的工作方式做了一些改进,但是与正确编写的基于集合的代码相比,“快速光标”这个词仍然是一个矛盾的说法。这一点在甲骨文中也同样适用。我在Oracle工作了3年,但我的工作是改进现有代码的性能。当我将光标转换为基于集合的代码时,实现了大部分真正的实质性改进。许多以前需要4到8个小时才能完成的工作被减少到几分钟甚至几秒钟。

使用光标的一些替代方法:

循环时 临时表 派生表 关联的子查询 案例陈述 多次询问 通常,光标操作也可以通过非光标技术实现。

如果确定需要使用光标,则应尽可能减少要处理的记录数。这样做的一种方法是先将要处理的记录放入临时表,而不是原始表,而是使用临时表中记录的光标。使用此路径时,假设与原始表相比,临时表中的记录数已大大减少。记录越少,光标完成得越快。

影响性能的一些光标属性包括:

仅前进:只支持将光标从第一行转到下一行提取的结尾。除非设置为keyset或static,否则在调用每个fetch时,将重新计算select子句。

静态:创建创建的数据的临时副本,并由光标使用。这样可以防止每次调用光标时重新计算光标,从而提高性能。这不允许修改光标类型,调用FETCH时不会反映对表的更改。

keyset:光标存储的行被放置在tempdb下的表中,调用fetch时,对非键列的更改会反映出来。但是,添加到表中的新记录不会反映出来。使用keyset光标时,不会再次计算select语句。

动态:对表的所有更改都反映在光标中。调用每次提取时,将重新计算光标。它使用大量资源,并对性能产生不利影响。

快进:光标是单向的,例如只前进,但将光标指定为只读。FORWARD U只会提高性能,而不会在每次提取时重新评估光标。如果适合编程,它将提供最佳性能。

乐观:此选项可用于更新光标中的行。如果提取和更新了一行,并且在提取和更新操作之间更新了另一行,则光标更新操作失败。如果使用可以执行行更新的乐观指针,则不应使用其他进程更新它。

注意:如果未指定cursore,则默认值为forward_only。

如果您想要一个比快进更快的光标,那么使用静态光标。他们比快进更快。不是非常快,但可以改变。