SQL游标内存开销
我有一个存储过程，适用于包含 xml 字段的表，并使用游标循环它们并运行一些其他 sp。行可能很多并且文档很大。有一些储存在负载下窒息的迹象。
我想知道这是否可能，因为游标是如何工作的，也许使用游标意味着它将整个结果集保留在内存中。我在心里总是想象游标就像在表上循环一样，而也许它更像是从表中选择数据，将其存储在内存中，然后在其上循环。光标如何工作？
附言。就我个人而言，我从性能和意识形态的角度讨厌游标（SQL 应该基于设置）并像瘟疫一样避免它们。这是一个遗留代码的情况，我现在已将其转换为使用临时表，但我仍然很好奇 DS。
I have an stored procedure that works on an table that contains xml field, and use a cursor to loop over them and run some other sp's. The rows can be many and the documents quite large. There has been some signs of the stored choking under the load.
I'm wondering if it's possible because how cursors work, perhaps using a cursor means it keep that whole result set in memory. I mentally somehow always imagine cursors like looping over a table while perhaps it's more like selecting data from a table, storing it in memory and then looping over it. How does cursors work?
PS. Personally I hate cursors from both a performance and ideological standpoint (SQL should be set based) and avoid them like plague. This was a case of legacy code that I now have converted to use a temp table instead, but I'm still curious DS.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
这将取决于您使用的光标类型。键集游标只会将 PK 存储在 tempdb 中，然后根据需要在其他列上进行查找。其他可能会存储一次检索更多信息、更多行、单行等。
以下是每种类型的详细描述：http://searchsqlserver.techtarget.com/feature/Part-1-How-cursors-work
It is going to be dependent on the type of cursor you are using. A keyset cursor will only store the PK in the tempdb and then do lookups on the other columns as needed. Others may store retrieve more information at one time, more rows, single rows, etc.
Here's a good description of each type: http://searchsqlserver.techtarget.com/feature/Part-1-How-cursors-work