MS SQL Server - 什么时候是CURSOR好？ [英] MS SQL Server - When is a CURSOR good?

问题描述

很多时候，我写了存储过程等。我使用CURSOR，然后在我的过程中发现一些性能问题。

Many times when I've written stored procedures, etc. I use a CURSOR at first and later find some performance issue with my procedure.

说CURSORS是可怕的，造成不必要的锁定等，性能测试证明是一样的。

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?

如果没有使用，为什么会为SQL创建这样一个坏的控制结构/类型？

If there is no use, why would they make such a bad control structure/type for SQL?

推荐答案

通常它们是要避免的，但是功能是有原因的，有时候使用它们。我想说，我看到的90％的游标不需要。如果您使用它们进行CRUD操作，那么几乎总是可以以基于集合的方式重做。我经常看到人们为此使用游标，因为他们不知道如何在更新或删除中使用连接，或者他们可以在插入中使用选择状态而不是值子句。另一种不必要的用法，当人们认为他们需要他们稍微更复杂的处理，实际上可以很容易地处理与case语句。

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.

游标有时更快计算像运行总计。

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.

如果您在SQl中创建电子邮件（不是最好的地方，但在某些系统中是他们做的地方），并且不希望电子邮件的所有受众都看到其他人在列表上，或者您想要个性化每个电子邮件，并提供有关收件人的信息，光标是可以走的。

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.

光标或循环也可用于处理批次记录如果整个基于集合的插入/更新/删除将花费太长时间并锁定表。这是一种在游标和基于集合的解决方案之间的混合，通常是生产系统上大型更改的最佳选择。

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.

这篇关于MS SQL Server - 什么时候是CURSOR好？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！