SQL 使用 FAST_FORWARD 定义游标有什么好处？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2280452/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is the advantage of using FAST_FORWARD for defining a cursor?
提问by masoud ramezani
What is the advantage of using FAST_FORWARD for defining a cursor? Is it better for performance? why?
使用 FAST_FORWARD 定义游标有什么好处？性能更好吗？为什么？
采纳答案by AdaTheDev
The definition from MSDNis:
MSDN的定义是：
Specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled. FAST_FORWARD cannot be specified if SCROLL or FOR_UPDATE is also specified. FAST_FORWARD and FORWARD_ONLY are mutually exclusive; if one is specified the other cannot be specified.
指定启用性能优化的 FORWARD_ONLY、READ_ONLY 游标。如果还指定了 SCROLL 或 FOR_UPDATE，则不能指定 FAST_FORWARD。FAST_FORWARD 和 FORWARD_ONLY 是互斥的；如果指定了一个，则不能指定另一个。
I've boldened the key bit. It can support these "performance optimisations" because it does not need to support multi-direction iterating through the cursor (FORWARD_ONLY) and does not support modifications (READ_ONLY).
我加粗了关键位。它可以支持这些“性能优化”，因为它不需要支持通过游标的多方向迭代（FORWARD_ONLY）并且不支持修改（READ_ONLY）。
Of course, if you don't really need to use a cursor at all - then using a cursor even with this option is not going to perform as well . If you can do the same task using a set-based approach, do that instead - this is the bit I really wanted to stress.
当然，如果您根本不需要使用游标 - 那么即使使用此选项使用游标也不会表现得很好。如果您可以使用基于集合的方法完成相同的任务，请改为这样做 - 这是我真正想强调的一点。
回答by Adeel
FAST_FORWARD- specifies that cursor will be FORWARD_ONLY and READ_ONLY cursor. The FAST_FORWARD cursors produce the least amount of overhead on SQL Server.
FAST_FORWARD- 指定游标将是 FORWARD_ONLY 和 READ_ONLY 游标。FAST_FORWARD 游标在 SQL Server 上产生的开销最少。
Source: Click Here
来源：点击这里
回答by Nick Craver
The FAST_FORWARDspecifies that it's FORWARD_ONLYand READ_ONLY, meaning it uses the least amount of server resources to handle it...so yes, for performance.
在FAST_FORWARD它的指定FORWARD_ONLY和READ_ONLY，这意味着它使用的服务器资源最少的处理呢......所以，是的，对性能。
MSDN has a full rundown of cursor options here.
FAST_FORWARD
- Specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled. FAST_FORWARD cannot be specified if SCROLL or FOR_UPDATE is also specified.
快进
- 指定启用了性能优化的 FORWARD_ONLY、READ_ONLY 游标。如果还指定了 SCROLL 或 FOR_UPDATE，则不能指定 FAST_FORWARD。
回答by George Moran
(I know this is old, but for posterity)
（我知道这是旧的，但为了后代）
Just to expatiate the "fast_forward" and "forward_only/read_only" cursors, the difference is in cursor plan usage.
只是为了说明“fast_forward”和“forward_only/read_only”游标，区别在于游标计划的使用。
FO/ROcursors always use a dynamic query plan - and for most applications, this is sufficient.
However, even a good dynamic plan is almost never as good as a static plan.
FO/RO游标总是使用动态查询计划 - 对于大多数应用程序，这已经足够了。然而，即使是好的动态计划也几乎永远不如静态计划。
FFcursors will use a static plan if it's better, and won't ever downgrade cursor plans (mostly what the "...with performance optimizations enabled." is referring to).
FF如果更好，游标将使用静态计划，并且永远不会降级游标计划（主要是“...启用性能优化。”所指的）。
Generally dynamic plans are more optimal for small result set ("low-goal") cursors, and vice-versa for static.
通常动态计划更适合小结果集（“低目标”）游标，反之亦然。
回答by Fabiano Novaes Ferreira
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
