在临时表上创建游标-安全吗？ [英] Creating a cursor on a temp table - is it safe?

问题描述

我知道在SQL中创建和使用游标既不安全也不高效，但有时是唯一的选择。现在，这是我唯一的选择。

I know that creating and using cursors in SQL is neither safe nor efficient but sometimes it's the only alternative. And right now it is the only alternative I have.

我的问题不是如何避免使用游标，而是如果游标仅在运行中动态创建的临时表上运行，会产生多大的安全性和性能问题？存储过程。我知道游标的运行速度比设置操作慢，并且会锁定要迭代的表。我的临时表是一个相对较小的表，仅包含一个int类型的字段，最多包含50条记录。

My question not how to avoid using cursors but how safe safe and what performance issues I will incur if the cursor is only operating on a temp table created on the fly within a stored procedure. I know cursors run slower than set operations and put a lock on tables that they're iterating. My temp table is a relatively small table containing just one field of type int and max 50 records.

DECLARE @pD int

DECLARE CurDB CURSOR FOR            
SELECT pD FROM #mD
open CurDB
fetch next from CurDB into @pD
etc...

推荐答案

是的，很安全！和不！游标通常不会在要迭代的表上加锁。
多数情况下，为了获得更好的性能，您可以使用FAST_FORWARD声明光标（如果它们没有更新表，则会对其进行迭代）。

Yes it's safe! And no! Cursors usually don't put a lock on the tables it's iterating. Also for better performance most of times you can declare your cursor with FAST_FORWARD (if they are no updating the table it's iterating over).

您可以看到关于声明光标

您还可以在99.9％的时间内使用关系逻辑编写一个不错的查询来完成游标可以完成的相同工作，但通常更快，并且使用的内存更少。

Also 99.9% of times you can write a nice query with relational logic to do the same work a cursor can do, but usually faster and using less memory.

实际上，游标不仅笨拙，而且在使用内存方面表现不佳，临时表还会消耗RAM（或tempdb，可能两者都有）。

In fact cursor are no only clumsy but they perform bad at using memory, temp tables also ill consume RAM (or the tempdb, probably both).

性能不仅时间和CPU周期有关，这与资源有关！

Performance is not only about time and CPU cycles, it's about resources!

这篇关于在临时表上创建游标-安全吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！