游标到底有多慢以及什么是更好的替代方案？
我一直在读到游标非常慢和一个除非没有选择，否则应该避免它们。我正在尝试优化我的存储过程，其中一个使用游标。它经常被我的应用程序调用，并且有大量用户（20000）和要更新的行。我想也许我应该使用其他东西作为替代。
我想做或想要做的就是获取记录列表，然后根据每行值进行操作。例如，我们说 -
Employee - Id,Name,BenefitId,StartDate,EndDate
因此，基于 BenefitId，我需要使用 StartDate 和 EndDate 之间的日期进行不同的计算，并更新员工详细信息。我只是做这个人为的例子来说明我的情况。
您对此有何看法？游标是否有更好的替代方案，例如使用临时表或用户定义的函数？您什么时候应该真正选择它们，或者我们不应该使用游标？感谢大家的帮助。
I have been reading that cursors are pretty slow and one should unless out of options avoid them. I am trying to optimize my stored procedures and one of them uses a cursor. It frequently is being called by my application and with lot of users(20000) and rows to update. I was thinking maybe I should use something else as an alternative.
All I am trying to do or want is to get a list of records and then operate on depending on each row value. So for e.g we have say -
Employee - Id,Name,BenefitId,StartDate,EndDate
So based on benefitId I need to do different calculation using dates between StartDate and EndDate and update employee details. I am just making this contrived example to give a idea on my situation.
What are your thoughts on it ? Are there better alternatives for cursors like say using temp tables or user defined functions? When should you really opt for them or should we never be using cursors ? Thanks everyone for their help.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
我曾经将存储过程从游标更改为基于集合的逻辑。运行时间从 8 小时缩短到 22 秒。这就是我们正在谈论的差异。
不要一次对一条记录采取不同的操作，而是对数据进行多次传递。更新并设置 field1=A，其中 field2 为 X，然后更新并设置 field1= B，其中 field2 为 Y，等等。
I once changed a stored procedure from cursors to set based logic. Running time went from 8 hours to 22 seconds. That's the kind of difference we're talking about.
Instead of taking different action a record at a time, use several passes on the data. Update and set field1=A where field2 is X, then update and set field1= B where field2 is Y, etc.
我更换了光标，并将处理时间从超过 24 小时缩短到不到一分钟。
为了帮助您了解如何使用基于集合的逻辑修复您的过程，请阅读以下内容：
http://wiki.lessthandot.com/index.php/Cursors_and_How_to_Avoid_Them
I've changed out cursors and moved from over 24 hours of processing time to less than a minute.
TO help you see how to fix your proc with set-based logic, read this:
http://wiki.lessthandot.com/index.php/Cursors_and_How_to_Avoid_Them
游标进行逐行处理，或者“逐行处理”（如果您的名字是 Jeff Moden）。
这只是 一个示例< /a> 如何进行基于集合的 SQL 编程而不是 RBAR，但这最终取决于您的游标正在做什么。
另外，请在 StackOverflow 上查看此内容：
RBAR 与基于 SQL 集的编程
A cursor does row-by-row processing, or "Row By Agonizing Row" if your name is Jeff Moden.
This is just one example of how to do set-based SQL programming as opposed to RBAR, but it depends ultimately on what your cursor is doing.
Also, have a look at this on StackOverflow:
RBAR vs. Set based programming for SQL
首先，听起来您正在尝试在存储过程中混合一些业务逻辑。这通常是您想要避免的事情。更好的解决方案是有一个封装业务逻辑的中间层。这样你的数据层仍然是纯粹的数据。
要回答您原来的问题，这实际上取决于您使用光标的用途。在某些情况下，您可以使用表变量或临时表。不过，您必须记住释放临时表，因此我建议尽可能使用表变量。但有时，没有办法绕过使用游标。也许最初的 DBA 没有规范化足够（或规范化太多），并且您被迫使用游标来遍历多个没有任何外键关系的表。
First off, it sounds like you are trying to mix some business logic in your stored procs. That's generally something you want to avoid. A better solution would be to have a middle tier layer which encapsulates that business logic. That way your data layer remains purely data.
To answer your original question, it really depends on what you are using the cursors for. In some cases you can use a table variable or a temp table. You have to remember to free up temp tables though so I would suggest using table variables whenever possible. Sometimes, though, there is just no way around using cursors. Maybe the original DBA's didn't normalize enough (or normalized too much) and you are forced to use a cursor to traverse through multiple tables without any foreign key relationships.