SQL Server 快进游标

SQL Server Fast Forward Cursors

提问by Miles D

It is generally accepted that the use of cursors in stored procedures should be avoided where possible (replaced with set based logic etc). If you take the cases where you need to iterate over some data, and can do in a read only manner, are fast forward (read only forward) cursor more or less inefficient than say while loops? From my investigations it looks as though the cursor option is generally faster and uses less reads and cpu time. I haven't done any extensive testing, but is this what others find? Do cursors of this type (fast forward) carry additional overhead or resource that could be expensive that I don't know about.

人们普遍认为，应尽可能避免在存储过程中使用游标（替换为基于集合的逻辑等）。如果您需要迭代某些数据并且可以以只读方式进行处理，那么快进（只读向前）游标是否比 while 循环效率低下？从我的调查来看，光标选项似乎通常更快，并且使用更少的读取和 CPU 时间。我没有做过任何广泛的测试，但这是其他人发现的吗？这种类型的游标（快进）是否会带来额外的开销或资源，这些开销或资源可能很昂贵，而我不知道。

Is all the talk about not using cursors really about avoiding the use of cursors when set-based approaches are available, and the use of updatable cursors etc.

所有关于不使用游标的讨论都是关于在基于集合的方法可用时避免使用游标，以及使用可更新游标等吗？

Thanks

谢谢

采纳答案by Simon Munro

The 'Best Practice' of avoiding cursors in SQL Server dates back to SQL Server 2000 and earlier versions. The rewrite of the engine in SQL 2005 addressed most of the issues related to the problems of cursors, particularly with the introduction of the fast forward option. Cursors are not neccessarily worse than set-based and are used extensively and successfully in Oracle PL/SQL (LOOP).

在 SQL Server 中避免游标的“最佳实践”可以追溯到 SQL Server 2000 和更早版本。SQL 2005 引擎的重写解决了与游标问题相关的大部分问题，特别是引入了快进选项。游标不一定比基于集合的差，并且在 Oracle PL/SQL (LOOP) 中被广泛且成功地使用。

The 'generally accepted' that you refer to wasvalid, but is now outdated and incorrect - go on the assumption that fast forward cursors behave as advertised and perform. Do some tests and research, basing your findings on SQL2005 and later

您所指的“普遍接受”是有效的，但现在已过时且不正确 - 继续假设快进游标的行为与广告和执行一样。做一些测试和研究，基于 SQL2005 和更高版本的发现

回答by Eric Z Beard

While a fast forward cursor does have some optimizations in Sql Server 2005, it is nottrue that they are anywhere close to a set based query in terms of performance. There are very few situations where cursor logic cannot be replaced by a set-based query. Cursors will always be inherently slower, due in part to the fact that you have to keep interrupting the execution in order to fill your local variables.

虽然快进游标在 Sql Server 2005 中确实有一些优化，但它们在性能方面并不接近基于集合的查询。游标逻辑不能被基于集合的查询替换的情况很少。游标本质上总是较慢，部分原因是您必须不断中断执行以填充局部变量。

Here are few references, which would only be the tip of the iceberg if you research this issue:

这里有一些参考资料，如果您研究这个问题，这只是冰山一角：

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

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

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

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

回答by GateKiller

People avoid cursor because they generally are more difficult to write than a simple while loops, however, a while loop can be expensive because your constantly selecting data from a table, temporary or otherwise.

人们避免使用游标，因为它们通常比简单的 while 循环更难编写，但是，while 循环可能很昂贵，因为您不断地从表中选择数据，临时的或其他的。

With a cursor, which is readonly fast forward, the data is kept in memory and has been specifically designed for looping.

使用只读快进游标，数据保存在内存中，并专门为循环而设计。

This articlehighlights that an average cursor runs 50 times faster than a while loop.

本文强调了平均游标的运行速度比 while 循环快 50 倍。

回答by Miles D

This answer hopes to consolidate the replies given to date.

这个回答希望能巩固迄今为止给出的答复。

1) If at all possible, used set based logic for your queries i.e. try and use just SELECT, INSERT, UPDATEor DELETEwith the appropriate FROMclauses or nested queries - these will almost always be faster.

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

2) If the above is not possible, then in SQL Server 2005+ FAST FORWARDcursors are efficient and perform well and should be used in preference to while loops.

2) 如果上述方法不可行，那么在 SQL Server 2005+ 中FAST FORWARD游标是高效且性能良好的，应该优先于 while 循环使用。

回答by MikeM

"If You want a even faster cursor than FAST FORWARD then use a STATIC cursor. They are faster than FAST FORWARD. Not extremely faster but can make a difference."

“如果你想要一个比 FAST FORWARD 更快的游标，那么使用 STATIC 游标。它们比 FAST FORWARD 快。不是特别快，但可以有所作为。”

Not so fast! According to Microsoft: "Typically, when these conversions occurred, the cursor type degraded to a ‘more expensive' cursor type. Generally, a (FAST) FORWARD-ONLY cursor is the most performant, followed by DYNAMIC, KEYSET, and finally STATIC which is generally the least performant."

没那么快！根据 Microsoft 的说法：“通常，当这些转换发生时，游标类型会降级为‘更昂贵’的游标类型。通常，(FAST) FORWARD-ONLY 游标的性能最高，其次是 DYNAMIC、KEYSET，最后是 STATIC通常是性能最低的。”

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

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

回答by Fabiano Novaes Ferreira

You can avoid cursors most of the time, but sometimes it's necessary.

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

Just keep in mind that FAST_FORWARD is DYNAMIC ... FORWARD_ONLY you can use with a STATIC cursor.

请记住， FAST_FORWARD 是 DYNAMIC ... FORWARD_ONLY 您可以与 STATIC 游标一起使用。

Try using it on the Halloween problem to see what happens !!!

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

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

回答by Burak Kartal

Some alternatives to using cursor:

使用游标的一些替代方法：

WHILE loops Temp tablolar Derived tables Associated subqueries CASE statements Multiple interrogations Often, cursor operations can also be achieved with non-cursor techniques.

WHILE 循环 临时表 派生表 关联子查询 CASE 语句 多次询问 通常，游标操作也可以使用非游标技术来实现。

If you are sure that the cursor needs to be used, the number of records to be processed should be reduced as much as possible. One way of doing this is to get the records to be processed first into a temp table, not the original table, but a cursor that will use the records in the temp table. When this path is used, it is assumed that the number of records in the temp table has been greatly reduced compared to the original table. With fewer records, the cursor completes faster.

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

Some cursor properties that affect performance include:

一些影响性能的游标属性包括：

FORWARD_ONLY: Supports forwarding only the cursor from the first row to the end with FETCH NEXT. Unless set as KEYSET or STATIC, the SELECT clause is re-evaluated when each fetch is called.

FORWARD_ONLY：支持使用 FETCH NEXT 仅将游标从第一行转发到末尾。除非设置为 KEYSET 或 STATIC，否则每次调用时都会重新评估 SELECT 子句。

STATIC: Creates a temp copy of the created data and is used by the cursor. This prevents the cursor from being recalculated each time it is called, which improves performance. This does not allow cursor type modification, and changes to the table are not reflected when the fetch is called.

STATIC：创建已创建数据的临时副本并由游标使用。这可以防止每次调用游标时重新计算游标，从而提高性能。这不允许修改游标类型，并且在调用 fetch 时不会反映对表的更改。

KEYSET: Cursored rows are placed in a table under tempdb, and changes to nonkey columns are reflected when the fetch is called. However, new records added to the table are not reflected. With the keyset cursor, the SELECT statement is not evaluated again.

KEYSET：光标所在的行放在 tempdb 下的一个表中，调用 fetch 时会反映对非键列的更改。但是，不会反映添加到表中的新记录。使用键集游标，不会再次评估 SELECT 语句。

DYNAMIC: All changes to the table are reflected in the cursore. The cursor is re-evaluated when each fetch is called. It uses a lot of resources and adversely affects performance.

动态：对表的所有更改都反映在光标中。每次调用时都会重新评估游标。它使用大量资源并对性能产生不利影响。

FAST_FORWARD: The cursor is one-way, such as FORWARD_ONLY, but specifies the cursor as read-only. FORWARD_ONLY is a performance increase and the cursor is not reevaluated every fetch. It gives the best performance if it is suitable for programming.

FAST_FORWARD：游标是单向的，如 FORWARD_ONLY，但指定游标为只读。FORWARD_ONLY 是一种性能提升，并且不会在每次获取时重新评估游标。如果它适合编程，它会提供最好的性能。

OPTIMISTIC: This option can be used to update rows in the cursor. If a row is fetched and updated, and another row is updated between fetch and update operations, the cursor update operation fails. If an OPTIMISTIC cursor is used that can perform line update, it should not be updated by another process.

OPTIMISTIC：此选项可用于更新游标中的行。如果获取并更新了一行，并且在获取和更新操作之间更新了另一行，则游标更新操作将失败。如果使用可以执行行更新的 OPTIMISTIC 游标，则不应由其他进程更新。

NOTE: If cursore is not specified, the default is FORWARD_ONLY.

注意：如果未指定 cursore，则默认值为 FORWARD_ONLY。

回答by Jeff Moden

To answer Mile's original questions...

要回答 Mile 的原始问题...

Fast Forward, Read Only, Static cursors (affectionately known as a "Fire Hose Cursor") are typically as fast or faster than a equivalent Temp Table and a While loop because such a cursor is nothing more than a Temp Table and a While loop that has been optimized a bit behind the scenes.

快进、只读、静态游标（被亲切地称为“消防软管游标”）通常与等效的临时表和 While 循环一样快或更快，因为这样的游标只不过是一个临时表和一个 While 循环，已经在幕后进行了一些优化。

To add to what Eric Z. Beard posted on this thread and to further answer the question of...

添加 Eric Z. Beard 在此线程上发布的内容并进一步回答...

"Is all the talk about not using cursors really about avoiding the use of cursors when set-based approaches are available, and the use of updatable cursors etc."

“所有关于不使用游标的讨论都是关于在基于集合的方法可用时避免使用游标，以及使用可更新游标等吗？”

Yes. With very few exceptions, it takes less time and less code to write proper set-based code to do the same thing as most cursors and has the added benefit of using much fewer resources and usually runs MUCH faster than a cursor or While loop. Generally speaking and with the exception of certain administrative tasks, they really should be avoided in favor of properly written set-based code. There are, of course, exceptions to every "rule" but, in the case of Cursors, While loops, and other forms of RBAR, most people can count the exceptions on one hand without using all of the fingers. ;-)

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

There's also the notion of "Hidden RBAR". This is code that looks set-based but actually isn't. This type of "set-based" code is the reason why certain people have embraced RBAR methods and say they're "OK". For example, solving the running total problem using an aggregated (SUM) correlated sub-query with an inequality in it to build the running total isn't really set-based in my book. Instead, it's RBAR on steroids because ,for each row calculated, it has to repeatedly "touch" many other rows at a rate of N*(N+1)/2. That's known as a "Triangular Join" and is at least half as bad as a full Cartesian Join (Cross Join or "Square Join").

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

Although MS has made some improvements in how Cursors work since SQL Server 2005, the term "Fast Cursor" is still an oxymoron compared to properly written set-based code. That also holds true even in Oracle. I worked with Oracle for a short 3 years in the past but my job was to make performance improvements in existing code. Most of the really substantial improvements were realized when I converted Cursors to set-based code. Many jobs that previously took 4 to 8 hours to execute were reduced to minutes and, sometimes, seconds.

尽管自 SQL Server 2005 以来 MS 对游标的工作方式进行了一些改进，但与正确编写的基于集合的代码相比，术语“快速游标”仍然是矛盾的。即使在 Oracle 中也是如此。过去我在 Oracle 工作了短短 3 年，但我的工作是改进现有代码的性能。当我将 Cursors 转换为基于集合的代码时，大部分真正实质性的改进都实现了。许多以前需要 4 到 8 小时才能执行的作业现在减少到几分钟，有时甚至几秒钟。

回答by Piotr Anders

If You want a even faster cursor than FAST FORWARD then use a STATIC cursor. They are faster than FAST FORWARD. Not extremely faster but can make a difference.

如果您想要比 FAST FORWARD 更快的游标，请使用 STATIC 游标。它们比快进快。不是非常快，但可以有所作为。