使用游标的优点和缺点（在SQL服务器中） [英] Pros and cons of using a cursor (in SQL server)
问题描述
我在这里问了一个问题在OLTP数据库（SQL服务器）中使用游标
我认为游标是非常强大的工具（我认为微软不支持游标的坏开发者）。假设你有一个表，其中一列中的值取决于上一行中相同列的值。如果它是一个后端进程，你不认为使用光标将是一个可以接受的选择吗？
在我的头顶我可以想到一个几个场景，我觉得应该没有耻辱使用光标。
1>一次性后端处理程序，用于清除在几分钟内完成执行的不良资料。
2>在长时间运行一次的批处理（类似每年一次）。
如果在上面的场景中，其他进程没有可见的压力，那么花费额外的时间编写代码来避免游标是不合理的吗？换句话说，在某些情况下，开发者的时间比一个对其他任何事物几乎没有影响的进程的性能更重要。
在我看来，这将是一些情况在那里你应该认真地尽量避免使用光标。
1>一个存储过程调用从一个网站，可以被调用非常频繁。
2>一个每天运行多次并消耗大量资源的SQL作业。
我认为它非常肤浅，游标不应该使用，而不分析手头的任务，实际上权衡它的替代品。
请让我知道您的想法。
其中游标实际上比基于集合的等价物更好的几种情况。运行总计是一个总是想到的 - 查找Itzik的话（并忽略任何涉及SQL Server 2012，它添加新的窗口函数，让游标在这种情况下为他们的钱运行）。
人们对游标的一个大问题是它们执行缓慢，他们使用临时存储等等。这部分是因为默认语法是一个全局光标与各种低效的默认选项。下一次你使用一个游标，不需要做像 UPDATE ... WHERE CURRENT OF （我已经能够避免我的整个事业），通过比较这两个语法选项，给它一个公平的摇动：
DECLARE c CURSOR
FOR< SELECT QUERY> ;;
DECLARE c CURSOR
LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR< SELECT QUERY> ;;
实际上，第一个版本代表了未记录的存储过程sp_MSforeachdb中的一个错误，执行期间任何数据库的状态更改。我随后编写了我自己的版本的存储过程（参见此处和此处 a>），它们都修复了错误（只需使用上述语法的后一版本），并添加了几个参数来控制将选择哪些数据库。
人们认为方法不是一个游标，因为它不说 DECLARE CURSOR 。我看到人们认为while循环比游标快（我希望我已经驱逐在这里）或使用 FOR XML PATH 执行组连接不是执行隐藏光标操作。在很多情况下查看计划将会显示真相。
在很多情况下，游标用于基于集合更合适的地方。但是有很多有效的使用情况，其中基于集合的等价物更复杂的写入，优化器生成一个计划，两者或不可能（例如维护任务，其中循环通过表更新统计信息，为结果中的每个值调用存储过程等）。对于许多大型多表查询也是如此，其中计划对于优化器处理太嚣张。在这些情况下，最好先将一些中间结果转储到临时结构中。对于一些基于集合的游标也是如此（如运行总计）。我还写了另一种方式，人们几乎总是本能地使用一个while循环/游标，并有更好的基于集合的替代方法。
UPDATE 2013-07-25
只是想添加一些额外的博客文章，我写的关于游标，你应该使用的选项，如果你 do 必须使用它们，并使用基于集合的查询而不是循环来生成集合：
运行总计的最佳方法 - 为SQL Server 2012更新
生成无循环的集或序列： [第1部分] [第2部分] < a href =http://www.sqlperformance.com/2013/01/t-sql-queries/generate-a-set-3> [第3部分]
I asked a question here Using cursor in OLTP databases (SQL server)
where people responded saying cursors should never be used.
I feel cursors are very powerful tools that are meant to be used (I don't think Microsoft supports cursors for bad developers).Suppose you have a table where the value of a column in a row is dependent on the value of the same column in the previous row. If it is a one time back end process, don't you think using a cursor would be an acceptable choice?
Off the top of my head I can think of a couple of scenarios where I feel there should be no shame in using cursors. Please let me know if you guys feel otherwise.
1>A one time back end process to clean bad data which completes execution within a few minutes. 2>Batch processes that run once in a long period of time (something like once a year). If in the above scenarios, there is no visible strain on the other processes, wouldn't it be unreasonable to spend extra hours writing code to avoid cursors? In other words in certain cases the developer's time is more important than the performance of a process that has almost no impact on anything else.
In my opinion these would be some scenarios where you should seriously try to avoid using a cursor. 1>A stored proc called from a website that can get called very often. 2>A SQL job that would run multiple times a day and consume a lot of resources.
I think its very superficial to make a general statement like "cursors should never be used" without analyzing the task at hand and actually weighing it against the alternatives.
Please let me know of your thoughts.
There are several scenarios where cursors actually perform better than set-based equivalents. Running totals is the one that always comes to mind - look for Itzik's words on that (and ignore any that involve SQL Server 2012, which adds new windowing functions that give cursors a run for their money in this situation).
One of the big problems people have with cursors is that they perform slowly, they use temporary storage, etc. This is partially because the default syntax is a global cursor with all kinds of inefficient default options. The next time you're doing something with a cursor that doesn't need to do things like UPDATE...WHERE CURRENT OF (which I've been able to avoid my entire career), give it a fair shake by comparing these two syntax options:
DECLARE c CURSOR
FOR <SELECT QUERY>;
DECLARE c CURSOR
LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR <SELECT QUERY>;
In fact the first version represents a bug in the undocumented stored procedure sp_MSforeachdb which makes it skip databases if the status of any database changes during execution. I subsequently wrote my own version of the stored procedure (see here and here) which both fixed the bug (simply by using the latter version of the syntax above) and added several parameters to control which databases would be chosen.
A lot of people think that a methodology is not a cursor because it doesn't say DECLARE CURSOR. I've seen people argue that a while loop is faster than a cursor (which I hope I've dispelled here) or that using FOR XML PATH to perform group concatenation is not performing a hidden cursor operation. Looking at the plan in a lot of cases will show the truth.
In a lot of cases cursors are used where set-based is more appropriate. But there are plenty of valid use cases where a set-based equivalent is much more complicated to write, for the optimizer to generate a plan for, both, or not possible (e.g. maintenance tasks where you're looping through tables to update statistics, calling a stored procedure for each value in a result, etc.). The same is true for a lot of big multi-table queries where the plan gets too monstrous for the optimizer to handle. In these cases it can be better to dump some of the intermediate results into a temporary structure first. The same goes for some set-based equivalents to cursors (like running totals). I've also written about the other way, where people almost always think instinctively to use a while loop / cursor and there are clever set-based alternatives that are much better.
UPDATE 2013-07-25
Just wanted to add some additional blog posts I've written about cursors, which options you should be using if you do have to use them, and using set-based queries instead of loops to generate sets:
Best Approaches for Running Totals - Updated for SQL Server 2012
What impact can different cursor options have?
Generate a Set or Sequence Without Loops: [Part 1] [Part 2] [Part 3]
这篇关于使用游标的优点和缺点（在SQL服务器中）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
