Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How does a database 'cursor' work?

With most drivers for most relational databases, the default and preferred way to access results is to use a cursor or iterator.

What I'm guessing is that the database does something like:

  1. Runs the query.
  2. Prepares the result, stores it in RAM?
  3. Returns the cursor for the result to the client.

Whenever the database driver gets a call to fetch the next result, it passes that cursor to the database, which gives the next result.

However, I'm not sure if that's really correct. One thing that stumps me is that if the database client and database server are on different nodes and communicating via the network, isn't this slow? Does it really use such a lazy approach? It makes sense not to return all the data, but is there some middle path it takes?

like image 624
Vanwaril Avatar asked Feb 18 '11 04:02

Vanwaril


People also ask

How does a cursor work in SQL?

In SQL procedures, a cursor make it possible to define a result set (a set of data rows) and perform complex logic on a row by row basis. By using the same mechanics, an SQL procedure can also define a result set and return it directly to the caller of the SQL procedure or to a client application.

Why do we use cursor in database?

A cursor keeps track of the position in the result set, and allows you to perform multiple operations row by row against a result set, with or without returning to the original table. In other words, cursors conceptually return a result set based on tables within the databases.

What is cursor in database with example?

Cursor is a Temporary Memory or Temporary Work Station. It is Allocated by Database Server at the Time of Performing DML(Data Manipulation Language) operations on Table by User. Cursors are used to store Database Tables. There are 2 types of Cursors: Implicit Cursors, and Explicit Cursors.

What is cursor in SQL Server and how it works?

A SQL Server cursor is a set of T-SQL logic to loop over a predetermined number of rows one at a time. The purpose for the cursor may be to update one row at a time or perform an administrative process such as SQL Server database backups in a sequential manner.


1 Answers

A cursor is a moving placement or pointer that indicates a position. English-speakers have used the term with this meaning since the 16th century, for a wide variety of movable or mobile position-markers. wikipedia description

It is supposed to conjure up the image of a cursor in a text editor. It is (in some contexts) a place holder for where the pointer (cursor) is in a given dataset. A row (i.e. a line) is returned with cursor.fetchone() and the cursor is advanced to the beginning of the next row/line.

The cursor abstracts how many rows are currently buffered at a database client. As the cursor nears the end of a buffer, the underlying framework will fetch more content. The defaults are usually a good guess at a good tradeoff between memory allocation, network latency and other factors.

It becomes a question of optimization. Google maps provides a good comparison. The user can pan around and it seems like the whole country map was downloaded to the client, but in reality it is downloading adjacent panels right before you need them.

Having the database client perform this buffering relieves the programmer from having to think about it before optimization is required.

like image 170
David W Avatar answered Sep 28 '22 05:09

David W