使用数据库游标的好处是什么？ [英] What are the benefits of using database cursor?

问题描述

这是基于面对的面试问题。

很短的定义可以

它可以用于操作查询返回的行
。

光标（点列出了 MSDN上的这里），我有一个问题在我看来，如果我们可以执行所有的操作使用查询或存储过程（如果我没有错，像我们可以使用Transact-SQL的ms-sql），是否有任何具体的点，我们应该使用cursor？ p>

解决方案

与大结果集相比，使用游标就像使用视频流而不是一个下载视频， 。
如果你下载，你必须有几个gigs的空间和耐心等待，直到下载完成。现在，无论您的机器或网络可能有多快，每个人都以相同的速度观看电影。

通常，任何查询都会发送到服务器，执行，结果集通过网络发送给您，在一次活动中。
光标将让你按行访问数据行，只有在你请求时才能流式传输（实际上可以查看它）。

• 光标可以节省您的时间 - 因为您不需要等待处理和下载完整的记录集


• 它会在服务器和因为他们不必为结果集分配大块内存。


• 负载平衡网络和服务器 - 以突发模式工作通常更高效，但它可以完全阻止您的服务器和您的网络。这种延迟对于多用户环境很少是理想的。


• 允许在不影响您的光标的情况下对查询的表执行操作（在特定条件下）。因此，当您在一个行上持有游标时，其他进程可以读取，更新甚至删除其他行。特别是对于非常忙碌的表，很多并发读取和写入。

• 一致性：使用游标，通常不会对数据的一致性快照进行操作，而是对行进行操作。所以你的并发/一致性/隔离保证从整个数据库（ACID）下降到只有一行。你通常可以通知你的DBMS你想要什么级别的并发，但是如果你太吝啬了（锁定你所在的完整的表），你会丢弃在服务器端的许多资源节约。




• 每行传输本身可能效率很低，因为每个数据包都有协商开销，您可以通过每个数据包发送大的，可能是压缩的数据块来避免。 （没有DB服务器或客户端库是愚蠢的，单独传输每一行，两端都有缓存和分块）。




• 更难做到正确。考虑具有大结果集的查询，激励您使用游标，该游标使用具有聚合函数的GROUP BY子句。 （这种查询在数据仓库中很常见）。 GROUP BY可以完全删除您的服务器，因为它必须立即生成和存储整个结果集，甚至可能在其他表上持有锁。

经验法则：

• 如果您处理小型快速创建的结果集，请不要使用游标。 / li>
  
• 游标在特别的，复杂的（排序），具有大结果集的顺序性质的查询和低一致性要求。

顺序性质表示在查询中的重GROUP BY子句中没有聚合函数。服务器可以懒洋洋地决定计算10行，让游标从缓存中消耗，同时处理其他东西。

HTH

It is based on the interview question that I faced.

Very short definition can be

It can be used to manipulate the rows returned by a query.

Besides the use of the cursor (Points are listed here on MSDN), I have a question in my mind that if we can perform all the operations using query or stored procedure (if I'm not wrong, Like we can use Transact-SQL for ms-sql), is there any concrete point that we should use cursor?

解决方案

Using cursors compared to big resultsets is like using video streaming instead of downloading an video in one swoop, and watching it when it has downloaded. If you download, you have to have a few gigs of space and the patience to wait until the download finished. Now, no matter how fast your machine or network may be, everyone watches a movie at the same speed.

Normally any query gets sent to the server, executed, and the resultset sent over the network to you, in one burst of activity. The cursor will give you access to the data row by row and stream every row only when you request it (can actually view it).

• A cursor can save you time - because you don't need to wait for the processing and download of your complete recordset
• It will save you memory, both on the server and on the client because they don't have to dedicate a big chunk of memory to resultsets
• Load-balance both your network and your server - Working in "burst" mode is usually more efficient, but it can completely block your server and your network. Such delays are seldom desirable for multiuser environments. Streaming leaves room for other operations.
• Allows operations on queried tables (under certain conditions) that do not affect your cursor directly. So while you are holding a cursor on a row, other processes are able to read, update and even delete other rows. This helps especially with very busy tables, many concurrent reads and writes.

Which brings us to some caveats, however:

• Consistency: Using a cursor, you do (usually) not operate on a consistent snapshot of the data, but on a row. So your concurrency/consistency/isolation guarantees drop from the whole database (ACID) to only one row. You can usually inform your DBMS what level of concurrency you want, but if you are too nitpicky (locking the complete table you are in), you will throw away many of the resource savings on the server side.

• Transmitting every row by itself can be very inefficient, since every packet has negotiation overhead that you might avoid by sending big, maybe compressed, chunks of data per packet. ( No DB server or client library is stupid enough to transmit every row individually, there's caching and chunking on both ends, still, it is relevant.)

• Cursors are harder to do right. Consider a query with a big resultset, motivating you to use a cursor, that uses a GROUP BY clause with aggregate functions. (Such queries are common in data warehouses). The GROUP BY can completely trash your server, because it has to generate and store the whole resultset at once, maybe even holding locks on other tables.

Rule of thumb:

• If you work on small, quickly created resultsets, don't use cursors.
• Cursors excell on ad hoc, complex (referentially), queries of sequential nature with big resultsets and low consistency requirements.

"Sequential nature" means there are no aggregate functions in heavy GROUP BY clauses in your query. The server can lazily decide to compute 10 rows for your cursor to consume from a cache and do other stuff meanwhile.

HTH

这篇关于使用数据库游标的好处是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！