Many times when I've written stored procedures, etc. I use a CURSOR at first and later find some performance issue with my procedure.
很多时候，当我编写存储过程等时，我首先使用游标，然后发现我的过程存在性能问题。
Every thing I read says CURSORS are awful, cause unnecessary locking, etc. and performance testing proves the same.
我读到的每一篇文章都说游标很糟糕，会导致不必要的锁定等等，性能测试也证明了这一点。
My question is when do you use a CURSOR and in what situations are they useful or good?
我的问题是什么时候使用光标，在什么情况下它们有用或有用?
If there is no use, why would they make such a bad control structure/type for SQL?
如果没有用处，他们为什么要为SQL创建如此糟糕的控制结构/类型呢?
9 个解决方案
#1
32
Normally they are to be avoided, but the feature is there for a reason and there are times to use them. I'd say 90+% of the cursors I've seen are not needed. If you are using them for CRUD operations, that can almost always be redone in a set-based fashion. I've often seen people use cursors for this because they don't know how to use joins in an update or delete or that they can use a select statment instead of a values clause in an insert. Another unnecessary use when people think they need them for slightly more complex processing that actually could easily be handled with a case statement.
通常应该避免它们，但是特性的存在是有原因的，并且有时间使用它们。我认为90%以上的游标是不需要的。如果您在CRUD操作中使用它们，几乎总是可以以基于集合的方式重新执行。我经常看到人们使用游标，因为他们不知道如何在更新或删除中使用连接，也不知道如何在插入中使用select语句而不是values子句。当人们认为需要它们进行稍微复杂一些的处理时，这是另一种不必要的使用，实际上可以用case语句轻松地处理。
Cursors are sometimes faster for calculating something like a running total.
游标有时在计算运行总数之类的东西时速度更快。
Cursors are also handy for multiple executions of a stored proc that is set up to handle only one input value at a time. I do not use this feature for running user stored procs (unless I know I will be hitting a very small set of data) but it is very handy for database admins when needing to run system procs against multiple tables.
游标对于一次只能处理一个输入值的存储proc的多次执行也很方便。我不使用这个特性来运行用户存储的procs(除非我知道我将访问一个非常小的数据集)，但是对于数据库管理员来说，当需要对多个表运行系统procs时，这是非常方便的。
If you are creating emails in SQl (not the best place to do it, but in some systems that's where they do it) and do not want the entire audience of the email to see the other people on the list or you want to personalize each email with information about the addressee, cursors are the way to go.
如果创建电子邮件在SQl(不是最好的地方,但在某些系统那是他们这样做),不希望整个邮件的观众看到名单上的人或你想每一封电子邮件都个人化。收件人信息,游标的路要走。
Cursors or loops can be used also to process batches of records if the entire set-based insert/update/delete will take too long and lock up the tables. This is a sort of a hybrid between the cursors and the set-based solution and is often the best one for large changes on production systems.
如果整个基于集合的插入/更新/删除过程太长并且锁住了表，则还可以使用游标或循环来处理成批的记录。这是游标和基于集合的解决方案之间的一种混合，通常是对生产系统进行重大更改的最佳解决方案。
#2
8
I asked a guy on the SQL Server team one time, if you could add one feature that would make the product better for everyone what would it be?
有一次我问SQL Server团队的一个人，你能不能添加一个功能，让产品更适合所有人?
His response was 'Add? Huh, I would take one away. If you get rid of cursors you force programmers all over the world to start thinking about things in a SET based way and that will be the biggest world wide increase in DB performance you will ever see.'
他的回答是“补充的吗?我要拿走一个。如果去掉游标，就会迫使世界各地的程序员开始以一种基于集合的方式思考问题，这将是全世界DB性能最大的提高。
For my part however I tend to see a pattern, there seems to be a lot of procedural coders who use cursors because they need to be able to do an operation one element at a time and miss the old fashion WHILE loop concept. Same basic idea without the cursor overhead. Still not near as fast/effective as something SET based but 90% of the time when someone claims 'I cant do this set based, I have to use cursors' I can get them to do it with a while loop.
然而，就我而言，我倾向于看到一个模式，似乎有很多使用游标的过程编码人员，因为他们需要能够一次执行一个操作一个元素，而忽略了老式的WHILE循环概念。同样的基本思想没有游标在头上。仍然不像某些设定的那样快/有效，但是90%的时候当某人声称“我不能做这个集合的时候，我必须使用游标”我可以让他们用while循环来做。
#3
1
Here's an article by a rather opinionated fellow, who gives reasoning for not using Cursors and some answers as to how they came to be: There Must be 15 Ways to Lose Your Cursors.
这是一个相当固执的家伙写的一篇文章，他给出了不使用游标的理由，并给出了一些关于它们是如何形成的答案:一定有15种方法可以失去游标。
#4
1
The MCTS prep manual for SQL Server 2008 that I'm studying recommends using external CLR code anywhere that a CURSOR would be required in T-SQL, especially now that SQL Server 2008 supports custom aggregate functions.
我正在研究的SQL Server 2008的MCTS准备手册建议在T-SQL中需要游标的任何地方使用外部CLR代码，特别是现在SQL Server 2008支持自定义聚合函数。
5 years ago, I worked with them for extensive reporting features, but I don't think I could come up with a good use case for them now. CLR aggregates and functions perform similarly to built-in aggregate functions.
5年前，我与他们一起开发了大量的报告特性，但我认为我现在无法为他们提供一个好的用例。CLR聚合和函数的执行类似于内置的聚合函数。
#5
1
Only time I'll use them is when whatever is been done inside the cursor absolutely has to be done one item at a time and where whatever is been done inside the cursor takes so long that the overhead of the cursor fades into insignificance.
我将使用它们的唯一时间是，当游标内所做的任何事情绝对必须一次只做一项时，以及游标内所做的任何事情都要花费如此长的时间，以至于游标的开销逐渐消失为无意义。
Eg database backups, integrity checks, index rebuilds. In short, admin tasks.
数据库备份，完整性检查，索引重建。简而言之,管理任务。
#6
1
OMG, how did I forget about Group By? I took the cursor based query you see below and replaced it with the one after it. Now I get a single result set so there are no issues with using sqlsrv_next_result() in php.
天啊，我怎么忘了团体By呢?我使用了下面的基于光标的查询，并将其替换为后面的那个。现在我得到了一个结果集，所以在php中使用sqlsrv_next_result()没有问题。
DECLARE @thisday datetime;
DECLARE daycursor CURSOR FOR
SELECT DISTINCT DATEADD(day, 0, DATEDIFF(day, 0, TimeCollected)) as thisday
FROM computerusedata
OPEN daycursor;
FETCH NEXT FROM daycursor
INTO @thisday;
WHILE @@FETCH_STATUS = 0
BEGIN
select distinct left(ComputerName,5) as CompGroup,DATEADD(day, 0, DATEDIFF(day, 0, TimeCollected)) as day
FROM computerusedata
where DATEADD(day, 0, DATEDIFF(day, 0, TimeCollected)) = @thisday
order by CompGroup;
FETCH NEXT FROM daycursor;
END;
CLOSE daycursor;
DEALLOCATE daycursor;";
select DATEADD(day, 0, DATEDIFF(day, 0, TimeCollected)) as day,left(ComputerName,5) as CompGroup
from ComputerUseData
group by DATEADD(day, 0, DATEDIFF(day, 0, TimeCollected)),left(ComputerName,5)
order by day,CompGroup
#7
0
I don't typically use cursors but when I do, it must be a "one-off" query that I'm running locally or a daily job. You want to refrain from having production code call a cursor that would be invoked frequently like in response to a web request.
我通常不使用游标，但当我使用游标时，它必须是一个“一次性”查询，我在本地运行或日常工作。您希望避免使用生产代码调用游标，就像响应web请求时经常调用游标一样。
#8
0
Cursors are useful when 1) you need to do something that you cannot do with a set operation, or 2) it doesn't make sense to do the same work by making iterative calls from the application layer. Or sometimes you have a procedure that must remain on the database layer, and you simply can't break back out to the app layer midstream to iterate over some result set.
当你需要做一些你不能做的事情时，游标是有用的，或者2)通过在应用层进行迭代调用来完成同样的工作是没有意义的。或者有时候你有一个必须保留在数据库层上的过程，你不能在应用程序层中间进行迭代以得到一些结果集。
One recommendation I would make though, is that people use cursor variables rather than normal cursors, because you avoid the cursor allocation/deallocation issues that surround normal cursors. With a normal cursor, if you don't deallocate them they persist, which can be a source of memory leaks. Not so with variable-based cursors (i.e. DECLARE @cursor CURSOR).
我的一个建议是，人们使用游标变量而不是普通的游标，因为您避免了围绕普通游标的游标分配/处理位置问题。对于一个普通的游标，如果不释放它们，它们将一直存在，这可能是内存泄漏的来源。基于变量的游标(即声明@cursor)则不是这样。
The bottom line is, avoid them if you possibly can, and if you can't, use them minimally and wisely.
最重要的是，如果可能的话，尽量避免使用它们，如果不行的话，尽量少地、明智地使用它们。
#9
0
You use cursor for rebuilding or reorganizing table indexes individually
unless there is a way of running ALTER INDEX... as a set-based operation.
您使用游标来分别重新构建或重组表索引，除非有一种运行ALTER INDEX的方法……作为一个基于集合的操作。
#1
32
Normally they are to be avoided, but the feature is there for a reason and there are times to use them. I'd say 90+% of the cursors I've seen are not needed. If you are using them for CRUD operations, that can almost always be redone in a set-based fashion. I've often seen people use cursors for this because they don't know how to use joins in an update or delete or that they can use a select statment instead of a values clause in an insert. Another unnecessary use when people think they need them for slightly more complex processing that actually could easily be handled with a case statement.
通常应该避免它们，但是特性的存在是有原因的，并且有时间使用它们。我认为90%以上的游标是不需要的。如果您在CRUD操作中使用它们，几乎总是可以以基于集合的方式重新执行。我经常看到人们使用游标，因为他们不知道如何在更新或删除中使用连接，也不知道如何在插入中使用select语句而不是values子句。当人们认为需要它们进行稍微复杂一些的处理时，这是另一种不必要的使用，实际上可以用case语句轻松地处理。
Cursors are sometimes faster for calculating something like a running total.
游标有时在计算运行总数之类的东西时速度更快。
Cursors are also handy for multiple executions of a stored proc that is set up to handle only one input value at a time. I do not use this feature for running user stored procs (unless I know I will be hitting a very small set of data) but it is very handy for database admins when needing to run system procs against multiple tables.
游标对于一次只能处理一个输入值的存储proc的多次执行也很方便。我不使用这个特性来运行用户存储的procs(除非我知道我将访问一个非常小的数据集)，但是对于数据库管理员来说，当需要对多个表运行系统procs时，这是非常方便的。
If you are creating emails in SQl (not the best place to do it, but in some systems that's where they do it) and do not want the entire audience of the email to see the other people on the list or you want to personalize each email with information about the addressee, cursors are the way to go.
如果创建电子邮件在SQl(不是最好的地方,但在某些系统那是他们这样做),不希望整个邮件的观众看到名单上的人或你想每一封电子邮件都个人化。收件人信息,游标的路要走。
Cursors or loops can be used also to process batches of records if the entire set-based insert/update/delete will take too long and lock up the tables. This is a sort of a hybrid between the cursors and the set-based solution and is often the best one for large changes on production systems.
如果整个基于集合的插入/更新/删除过程太长并且锁住了表，则还可以使用游标或循环来处理成批的记录。这是游标和基于集合的解决方案之间的一种混合，通常是对生产系统进行重大更改的最佳解决方案。
#2
8
I asked a guy on the SQL Server team one time, if you could add one feature that would make the product better for everyone what would it be?
有一次我问SQL Server团队的一个人，你能不能添加一个功能，让产品更适合所有人?
His response was 'Add? Huh, I would take one away. If you get rid of cursors you force programmers all over the world to start thinking about things in a SET based way and that will be the biggest world wide increase in DB performance you will ever see.'
他的回答是“补充的吗?我要拿走一个。如果去掉游标，就会迫使世界各地的程序员开始以一种基于集合的方式思考问题，这将是全世界DB性能最大的提高。
For my part however I tend to see a pattern, there seems to be a lot of procedural coders who use cursors because they need to be able to do an operation one element at a time and miss the old fashion WHILE loop concept. Same basic idea without the cursor overhead. Still not near as fast/effective as something SET based but 90% of the time when someone claims 'I cant do this set based, I have to use cursors' I can get them to do it with a while loop.
然而，就我而言，我倾向于看到一个模式，似乎有很多使用游标的过程编码人员，因为他们需要能够一次执行一个操作一个元素，而忽略了老式的WHILE循环概念。同样的基本思想没有游标在头上。仍然不像某些设定的那样快/有效，但是90%的时候当某人声称“我不能做这个集合的时候，我必须使用游标”我可以让他们用while循环来做。
#3
1
Here's an article by a rather opinionated fellow, who gives reasoning for not using Cursors and some answers as to how they came to be: There Must be 15 Ways to Lose Your Cursors.
这是一个相当固执的家伙写的一篇文章，他给出了不使用游标的理由，并给出了一些关于它们是如何形成的答案:一定有15种方法可以失去游标。
#4
1
The MCTS prep manual for SQL Server 2008 that I'm studying recommends using external CLR code anywhere that a CURSOR would be required in T-SQL, especially now that SQL Server 2008 supports custom aggregate functions.
我正在研究的SQL Server 2008的MCTS准备手册建议在T-SQL中需要游标的任何地方使用外部CLR代码，特别是现在SQL Server 2008支持自定义聚合函数。
5 years ago, I worked with them for extensive reporting features, but I don't think I could come up with a good use case for them now. CLR aggregates and functions perform similarly to built-in aggregate functions.
5年前，我与他们一起开发了大量的报告特性，但我认为我现在无法为他们提供一个好的用例。CLR聚合和函数的执行类似于内置的聚合函数。
#5
1
Only time I'll use them is when whatever is been done inside the cursor absolutely has to be done one item at a time and where whatever is been done inside the cursor takes so long that the overhead of the cursor fades into insignificance.
我将使用它们的唯一时间是，当游标内所做的任何事情绝对必须一次只做一项时，以及游标内所做的任何事情都要花费如此长的时间，以至于游标的开销逐渐消失为无意义。
Eg database backups, integrity checks, index rebuilds. In short, admin tasks.
数据库备份，完整性检查，索引重建。简而言之,管理任务。
#6
1
OMG, how did I forget about Group By? I took the cursor based query you see below and replaced it with the one after it. Now I get a single result set so there are no issues with using sqlsrv_next_result() in php.
天啊，我怎么忘了团体By呢?我使用了下面的基于光标的查询，并将其替换为后面的那个。现在我得到了一个结果集，所以在php中使用sqlsrv_next_result()没有问题。
DECLARE @thisday datetime;
DECLARE daycursor CURSOR FOR
SELECT DISTINCT DATEADD(day, 0, DATEDIFF(day, 0, TimeCollected)) as thisday
FROM computerusedata
OPEN daycursor;
FETCH NEXT FROM daycursor
INTO @thisday;
WHILE @@FETCH_STATUS = 0
BEGIN
select distinct left(ComputerName,5) as CompGroup,DATEADD(day, 0, DATEDIFF(day, 0, TimeCollected)) as day
FROM computerusedata
where DATEADD(day, 0, DATEDIFF(day, 0, TimeCollected)) = @thisday
order by CompGroup;
FETCH NEXT FROM daycursor;
END;
CLOSE daycursor;
DEALLOCATE daycursor;";
select DATEADD(day, 0, DATEDIFF(day, 0, TimeCollected)) as day,left(ComputerName,5) as CompGroup
from ComputerUseData
group by DATEADD(day, 0, DATEDIFF(day, 0, TimeCollected)),left(ComputerName,5)
order by day,CompGroup
#7
0
I don't typically use cursors but when I do, it must be a "one-off" query that I'm running locally or a daily job. You want to refrain from having production code call a cursor that would be invoked frequently like in response to a web request.
我通常不使用游标，但当我使用游标时，它必须是一个“一次性”查询，我在本地运行或日常工作。您希望避免使用生产代码调用游标，就像响应web请求时经常调用游标一样。
#8
0
Cursors are useful when 1) you need to do something that you cannot do with a set operation, or 2) it doesn't make sense to do the same work by making iterative calls from the application layer. Or sometimes you have a procedure that must remain on the database layer, and you simply can't break back out to the app layer midstream to iterate over some result set.
当你需要做一些你不能做的事情时，游标是有用的，或者2)通过在应用层进行迭代调用来完成同样的工作是没有意义的。或者有时候你有一个必须保留在数据库层上的过程，你不能在应用程序层中间进行迭代以得到一些结果集。
One recommendation I would make though, is that people use cursor variables rather than normal cursors, because you avoid the cursor allocation/deallocation issues that surround normal cursors. With a normal cursor, if you don't deallocate them they persist, which can be a source of memory leaks. Not so with variable-based cursors (i.e. DECLARE @cursor CURSOR).
我的一个建议是，人们使用游标变量而不是普通的游标，因为您避免了围绕普通游标的游标分配/处理位置问题。对于一个普通的游标，如果不释放它们，它们将一直存在，这可能是内存泄漏的来源。基于变量的游标(即声明@cursor)则不是这样。
The bottom line is, avoid them if you possibly can, and if you can't, use them minimally and wisely.
最重要的是，如果可能的话，尽量避免使用它们，如果不行的话，尽量少地、明智地使用它们。
#9
0
You use cursor for rebuilding or reorganizing table indexes individually
unless there is a way of running ALTER INDEX... as a set-based operation.
您使用游标来分别重新构建或重组表索引，除非有一种运行ALTER INDEX的方法……作为一个基于集合的操作。