为什么在 SQL Server 中使用游标被认为是不好的做法? [英] Why is it considered bad practice to use cursors in SQL Server?

问题描述

我在 SQL 7 天前就知道一些性能原因，但 SQL Server 2005 中是否仍然存在相同的问题?如果我想单独操作存储过程中的结果集，游标仍然是一个糟糕的选择吗?如果是，为什么?

I knew of some performance reasons back in the SQL 7 days, but do the same issues still exist in SQL Server 2005? If I have a resultset in a stored procedure that I want to act upon individually, are cursors still a bad choice? If so, why?

推荐答案

因为游标占用内存并创建锁.

Because cursors take up memory and create locks.

您真正在做的是试图将基于集合的技术强制转换为非基于集合的功能.而且，平心而论，我应该指出游标确实有用，但他们不赞成，因为许多不习惯使用基于集合的解决方案的人使用游标而不是弄清楚集合基于解决方案.

What you are really doing is attempting to force set-based technology into non-set based functionality. And, in all fairness, I should point out that cursors do have a use, but they are frowned upon because many folks who are not used to using set-based solutions use cursors instead of figuring out the set-based solution.

但是，当您打开游标时，您基本上是将这些行加载到内存中并锁定它们，从而创建潜在的块.然后，当您在游标中循环时，您正在对其他表进行更改，并且仍然保持游标的所有内存和锁处于打开状态.

But, when you open a cursor, you are basically loading those rows into memory and locking them, creating potential blocks. Then, as you cycle through the cursor, you are making changes to other tables and still keeping all of the memory and locks of the cursor open.

所有这些都有可能导致其他用户出现性能问题.

All of which has the potential to cause performance issues for other users.

因此，作为一般规则，游标是不受欢迎的.特别是如果这是解决问题的第一个解决方案.

So, as a general rule, cursors are frowned upon. Especially if that's the first solution arrived at in solving a problem.

这篇关于为什么在 SQL Server 中使用游标被认为是不好的做法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！