Is it a good idea to use while loop instead of a cursor? What are the advantages/disadvantages of cursors?
使用while循环而不是游标是一个好主意吗？游标有哪些优点/缺点？
4 个解决方案
#1
9
Some of these depends on the DBMS, but generally:
其中一些取决于DBMS，但通常：
Pros:
优点：
-
Outperform loops when it comes to row-by-row processing
在逐行处理方面表现优于循环
-
Works reasonably well with large datasets
适用于大型数据集
Cons:
缺点：
-
Don't scale as well
不要扩展
-
Use more server resources
使用更多服务器资源
-
Increases load on tempdb
增加tempdb的负载
-
Can cause leaks if used incorrectly (eg. Open without corresponding Close)
如果使用不当会导致泄漏（例如，打开时没有相应的关闭）
#2
10
I'm following this bit of advice:
我正在遵循这一点建议：
[...] which is better: cursors or WHILE loops? Again, it really depends on your situation. I almost always use a cursor to loop through records when necessary. The cursor format is a little more intuitive for me and, since I just use the constructs to loop through the result set once, it makes sense to use the FAST_FORWARD cursor. Remember that the type of cursor you use will have a huge impact on the performance of your looping construct.
[...]哪个更好：游标或WHILE循环？再次，它真的取决于你的情况。我几乎总是在必要时使用游标循环遍历记录。游标格式对我来说更直观一些，因为我只是使用构造来遍历结果集一次，所以使用FAST_FORWARD游标是有意义的。请记住，您使用的游标类型将对循环结构的性能产生巨大影响。
— Tim Chapman in Comparing cursor vs. WHILE loop performance in SQL Server 2008
- Tim Chapman在SQL Server 2008中比较游标与WHILE循环性能
The linked article contains simple examples of how to implement each approach.
链接的文章包含如何实现每种方法的简单示例。
#3
1
I would ask you what you are doing with that cursor/while loop.
我会问你用光标/ while循环做什么。
If you are updating or returning data why don't you use a proper WHERE clause. I know people who would say you should never use cursors.
如果要更新或返回数据，为什么不使用正确的WHERE子句。我知道有人会说你永远不应该使用游标。
#4
#1
9
Some of these depends on the DBMS, but generally:
其中一些取决于DBMS，但通常：
Pros:
优点：
-
Outperform loops when it comes to row-by-row processing
在逐行处理方面表现优于循环
-
Works reasonably well with large datasets
适用于大型数据集
Cons:
缺点：
-
Don't scale as well
不要扩展
-
Use more server resources
使用更多服务器资源
-
Increases load on tempdb
增加tempdb的负载
-
Can cause leaks if used incorrectly (eg. Open without corresponding Close)
如果使用不当会导致泄漏（例如，打开时没有相应的关闭）
#2
10
I'm following this bit of advice:
我正在遵循这一点建议：
[...] which is better: cursors or WHILE loops? Again, it really depends on your situation. I almost always use a cursor to loop through records when necessary. The cursor format is a little more intuitive for me and, since I just use the constructs to loop through the result set once, it makes sense to use the FAST_FORWARD cursor. Remember that the type of cursor you use will have a huge impact on the performance of your looping construct.
[...]哪个更好：游标或WHILE循环？再次，它真的取决于你的情况。我几乎总是在必要时使用游标循环遍历记录。游标格式对我来说更直观一些，因为我只是使用构造来遍历结果集一次，所以使用FAST_FORWARD游标是有意义的。请记住，您使用的游标类型将对循环结构的性能产生巨大影响。
— Tim Chapman in Comparing cursor vs. WHILE loop performance in SQL Server 2008
- Tim Chapman在SQL Server 2008中比较游标与WHILE循环性能
The linked article contains simple examples of how to implement each approach.
链接的文章包含如何实现每种方法的简单示例。
#3
1
I would ask you what you are doing with that cursor/while loop.
我会问你用光标/ while循环做什么。
If you are updating or returning data why don't you use a proper WHERE clause. I know people who would say you should never use cursors.
如果要更新或返回数据，为什么不使用正确的WHERE子句。我知道有人会说你永远不应该使用游标。