SQL Server 快进游标 [英] SQL Server Fast Forward Cursors

问题描述

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

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.

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

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.

谢谢

推荐答案

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

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).

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

The 'generally accepted' that you refer to was valid, 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

这篇关于SQL Server 快进游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！