当前位置：文江博客话题详情

为什么在 SQL Server 中使用游标被认为是不好的做法？

发布于 2024-07-04 08:09:01 字数 114 浏览 11 评论 0原文

我早在 SQL 7 时代就知道一些性能原因，但是 SQL Server 2005 中是否仍然存在同样的问题？如果我想对存储过程中有一个结果集单独进行操作，那么游标仍然是一个糟糕的选择吗？如果是这样，为什么？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

揽月 2024-07-11 08:09:02

游标确实有一席之地，但我认为这主要是因为它们经常在单个 select 语句足以提供结果聚合和过滤时使用。

避免游标可以让 SQL Server 更全面地优化查询性能，这在大型系统中非常重要。

回复收藏 0 原文

若相惜即相离 2024-07-11 08:09:02

我认为，基本问题是数据库是为基于集合的操作而设计和调整的——根据数据中的关系在一个快速步骤中选择、更新和删除大量数据。

另一方面，内存软件是为单独的操作而设计的，因此循环一组数据并可能对每个项目串行执行不同的操作是它最擅长的。

循环不是数据库或存储架构的设计目的，即使在 SQL Server 2005 中，如果将基本数据集拉入自定义程序并在内存中执行循环，您也无法获得与您所获得的性能相近的性能。，使用尽可能轻量的数据对象/结构。

回复收藏 0 原文

鞋纸虽美，但不合脚ㄋ〞 2024-07-11 08:09:01

在非常非常少的情况下，使用游标是合理的。几乎在任何情况下它都会优于基于集合的关系查询。有时，程序员用循环来思考会更容易，但是使用集合逻辑（例如更新表中的大量行）将导致解决方案不仅减少了 SQL 代码行，但运行速度要快得多，通常快几个数量级。

即使是 Sql Server 2005 中的快进游标也无法与基于集合的查询竞争。与基于集合的操作相比，性能下降的图表通常开始看起来像 n^2 操作，随着数据集变得非常大，它往往更加线性。

回复收藏 0 原文

世界等同你 2024-07-11 08:09:01

@丹尼尔P-> 您不需要使用光标来完成此操作。您可以轻松地使用基于集合的理论来做到这一点。例如：使用 Sql 2008

DECLARE @commandname NVARCHAR(1000) = '';

SELECT @commandname += 'truncate table ' + tablename + '; ';
FROM tableNames;

EXEC sp_executesql @commandname;

将简单地执行您上面所说的操作。您可以对 Sql 2000 执行相同的操作，但查询的语法会有所不同。

不过，我的建议是尽可能避免使用游标。

迦耶姆

@ Daniel P -> you don't need to use a cursor to do it. You can easily use set based theory to do it. Eg: with Sql 2008

DECLARE @commandname NVARCHAR(1000) = '';

SELECT @commandname += 'truncate table ' + tablename + '; ';
FROM tableNames;

EXEC sp_executesql @commandname;

will simply do what you have said above. And you can do the same with Sql 2000 but the syntax of query would be different.

However, my advice is to avoid cursors as much as possible.

Gayam

回复收藏 0 原文

倚栏听风 2024-07-11 08:09:01

有时，您需要执行的处理的性质需要游标，但出于性能原因，如果可能的话，使用基于集合的逻辑编写操作总是更好。

我不会称使用游标为“不好的做法”，但它们确实消耗了服务器上更多的资源（比等效的基于集的方法），而且通常它们是不必要的。鉴于此，我的建议是在诉诸游标之前考虑其他选项。

有多种类型的游标（仅向前、静态、键集、动态）。每一种都有不同的性能特征和相关的开销。确保您的操作使用正确的光标类型。仅转发是默认设置。

使用游标的一个理由是当您需要处理和更新单个行时，尤其是对于没有良好唯一键的数据集。在这种情况下，您可以在声明游标时使用 FOR UPDATE 子句，并使用 UPDATE ... WHERE CURRENT OF 处理更新。

请注意，“服务器端”游标曾经很流行（来自 ODBC 和 OLE DB），但 ADO.NET 不支持它们，并且 AFAIK 永远不会支持它们。

回复收藏 0 原文

天邊彩虹 2024-07-11 08:09:01

SQL 是一种基于集合的语言——这正是它最擅长的地方。

我认为游标仍然是一个糟糕的选择，除非您对它们有足够的了解以证明它们在有限的情况下使用是合理的。

我不喜欢光标的另一个原因是清晰度。光标块太丑了，很难以清晰有效的方式使用。

综上所述，在某些情况下光标确实是最好的——只是初学者通常不想使用它们。

回复收藏 0 原文

冬天的雪花 2024-07-11 08:09:01

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

您真正所做的是尝试将基于集合的技术强制引入非基于集合的功能。而且，平心而论，我应该指出游标确实有用途，但人们不赞成它们，因为许多不习惯使用基于集合的解决方案的人使用游标而不是找出集合基于解决方案。

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

所有这些都有可能给其他用户带来性能问题。

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

回复收藏 0 原文

你是暖光i 2024-07-11 08:09:01

上面关于 SQL 是基于集合的环境的评论都是正确的。然而，有时逐行操作很有用。考虑元数据和动态 SQL 的组合。

举一个非常简单的例子，假设我在一个表中有 100 多条记录，这些记录定义了我想要复制/截断/任何内容的表的名称。哪个最好？对 SQL 进行硬编码以执行我需要的操作？或者迭代此结果集并使用动态 SQL (sp_executesql) 来执行操作？

使用基于集合的 SQL 无法实现上述目标。

那么，使用游标还是 while 循环（伪游标）？

只要您使用正确的选项，SQL 游标就可以：

INSENSITIVE 将为您的结果集创建一个临时副本（使您不必自己为伪游标执行此操作）。

READ_ONLY 将确保基础结果集上不持有任何锁。基础结果集的更改将反映在后续的提取中（与从伪光标获取 TOP 1 相同）。

FAST_FORWARD 将创建一个优化的只进、只读游标。

在将所有光标判定为邪恶之前，请先阅读可用选项。

回复收藏 0 原文

将军与妓 2024-07-11 08:09:01

有一种关于游标的解决方法，我每次需要时都会使用它。

我创建一个表变量，其中包含标识列。

将我需要使用的所有数据插入其中。

然后使用计数器变量创建一个 while 块，并使用 select 语句从表变量中选择我想要的数据，其中标识列与计数器匹配。

这样我就不会锁定任何东西，并且使用更少的内存并且它是安全的，我不会因为内存损坏或类似的事情而丢失任何东西。

并且块代码很容易查看和处理。

这是一个简单的例子：

DECLARE @TAB TABLE(ID INT IDENTITY, COLUMN1 VARCHAR(10), COLUMN2 VARCHAR(10))

DECLARE @COUNT INT,
        @MAX INT, 
        @CONCAT VARCHAR(MAX), 
        @COLUMN1 VARCHAR(10), 
        @COLUMN2 VARCHAR(10)

SET @COUNT = 1

INSERT INTO @TAB VALUES('TE1S', 'TE21')
INSERT INTO @TAB VALUES('TE1S', 'TE22')
INSERT INTO @TAB VALUES('TE1S', 'TE23')
INSERT INTO @TAB VALUES('TE1S', 'TE24')
INSERT INTO @TAB VALUES('TE1S', 'TE25')

SELECT @MAX = @@IDENTITY

WHILE @COUNT <= @MAX BEGIN
    SELECT @COLUMN1 = COLUMN1, @COLUMN2 = COLUMN2 FROM @TAB WHERE ID = @COUNT

    IF @CONCAT IS NULL BEGIN
        SET @CONCAT = '' 
    END ELSE BEGIN 
        SET @CONCAT = @CONCAT + ',' 
    END

    SET @CONCAT = @CONCAT + @COLUMN1 + @COLUMN2

    SET @COUNT = @COUNT + 1
END

SELECT @CONCAT

There is a work around about cursors that I use every time I need one.

I create a table variable with an identity column in it.

insert all the data i need to work with in it.

Then make a while block with a counter variable and select the data I want from the table variable with a select statement where the identity column matches the counter.

This way i dont lock anything and use alot less memory and its safe, i will not lose anything with a memory corruption or something like that.

And the block code is easy to see and handle.

This is a simple example:

DECLARE @TAB TABLE(ID INT IDENTITY, COLUMN1 VARCHAR(10), COLUMN2 VARCHAR(10))

DECLARE @COUNT INT,
        @MAX INT, 
        @CONCAT VARCHAR(MAX), 
        @COLUMN1 VARCHAR(10), 
        @COLUMN2 VARCHAR(10)

SET @COUNT = 1

INSERT INTO @TAB VALUES('TE1S', 'TE21')
INSERT INTO @TAB VALUES('TE1S', 'TE22')
INSERT INTO @TAB VALUES('TE1S', 'TE23')
INSERT INTO @TAB VALUES('TE1S', 'TE24')
INSERT INTO @TAB VALUES('TE1S', 'TE25')

SELECT @MAX = @@IDENTITY

WHILE @COUNT <= @MAX BEGIN
    SELECT @COLUMN1 = COLUMN1, @COLUMN2 = COLUMN2 FROM @TAB WHERE ID = @COUNT

    IF @CONCAT IS NULL BEGIN
        SET @CONCAT = '' 
    END ELSE BEGIN 
        SET @CONCAT = @CONCAT + ',' 
    END

    SET @CONCAT = @CONCAT + @COLUMN1 + @COLUMN2

    SET @COUNT = @COUNT + 1
END

SELECT @CONCAT

回复收藏 0 原文

如痴如狂 2024-07-11 08:09:01

我认为游标的名声不好，因为 SQL 新手发现它们后会想“嘿，for 循环！我知道如何使用它们！” 然后他们继续将它们用于所有事情。

如果你按照它们的设计目的来使用它们，我对此无可挑剔。

回复收藏 0 原文

孤蝉 2024-07-11 08:09:01

游标通常不是疾病，而是它的症状：不使用基于集合的方法（如其他答案中提到的）。

不理解这个问题，并且简单地相信避免“邪恶”光标就能解决它，可能会让事情变得更糟。

例如，用其他迭代代码替换游标迭代，例如将数据移动到临时表或表变量，以如下方式循环行：

SELECT * FROM @temptable WHERE Id=@counter

或

SELECT TOP 1 * FROM @temptable WHERE Id>@lastId

这种方法，如另一个答案的代码所示，会使事情变得更糟并且没有解决原来的问题。这是一种反模式，称为货物邪教编程：不知道为什么某些东西不好，因此实施更糟糕的东西来避免它！我最近将此类代码（使用 #temptable 并且在身份/PK 上没有索引）更改回游标，并且更新略多于 10000 行只花费了 1 秒而不是近 3 分钟。仍然缺乏基于集合的方法（两害相权取其轻），但那一刻我能做到的最好的了。

这种缺乏理解的另一个症状可能是我有时所说的“单一对象疾病”：通过数据访问层或对象关系映射器处理单个对象的数据库应用程序。通常的代码如下：

var items = new List<Item>();
foreach(int oneId in itemIds)
{
    items.Add(dataAccess.GetItemById(oneId);
}

而不是

var items = dataAccess.GetItemsByIds(itemIds);

第一个通常会用大量的 SELECT 淹没数据库，每个 SELECT 一次往返，特别是当对象树/图发挥作用并且臭名昭著的 SELECT N+1 问题出现时。

这是不理解关系数据库和基于集合的方法的应用程序方面，就像使用过程数据库代码（如 T-SQL 或 PL/SQL）时游标的方式一样！

Cursors are usually not the disease, but a symptom of it: not using the set-based approach (as mentioned in the other answers).

Not understanding this problem, and simply believing that avoiding the "evil" cursor will solve it, can make things worse.

For example, replacing cursor iteration by other iterative code, such as moving data to temporary tables or table variables, to loop over the rows in a way like:

SELECT * FROM @temptable WHERE Id=@counter

SELECT TOP 1 * FROM @temptable WHERE Id>@lastId

Such an approach, as shown in the code of another answer, makes things much worse and doesn't fix the original problem. It's an anti-pattern called cargo cult programming: not knowing WHY something is bad and thus implementing something worse to avoid it! I recently changed such code (using a #temptable and no index on identity/PK) back to a cursor, and updating slightly more than 10000 rows took only 1 second instead of almost 3 minutes. Still lacking set-based approach (being the lesser evil), but the best I could do that moment.

Another symptom of this lack of understanding can be what I sometimes call "one object disease": database applications which handle single objects through data access layers or object-relational mappers. Typically code like:

var items = new List<Item>();
foreach(int oneId in itemIds)
{
    items.Add(dataAccess.GetItemById(oneId);
}

instead of

var items = dataAccess.GetItemsByIds(itemIds);

The first will usually flood the database with tons of SELECTs, one round trip for each, especially when object trees/graphs come into play and the infamous SELECT N+1 problem strikes.

This is the application side of not understanding relational databases and set based approach, just the same way cursors are when using procedural database code, like T-SQL or PL/SQL!

回复收藏 0 原文

~没有更多了~