Original text excerpted from: http://www.cnblogs.com/moss_tan_jun/archive/2011/11/26/2263988.html
Cursors are evil!
In relational databases, our thinking about queries is set-oriented. The cursor breaks this rule. The cursor makes our way of thinking change to line by line. For C-like developers, this way of thinking will be more comfortable.
The normal way of thinking about collections is:
And for cursors:
This is why cursors are evil, they make developers lazy and too lazy to think about implementing certain functions using set-oriented queries.
Likewise, in terms of performance, cursors will eat more memory, reduce available concurrency, occupy bandwidth, lock resources, and of course require more code…
From the way the cursor reads the database, it is not difficult to see why the cursor takes up more resources. For example:
When you withdraw money from an ATM, is it more efficient to withdraw 1,000 at once, or 100?
Since cursors are so “evil”, why do we need to learn cursors
I personally think that existence is reasonable. In the final analysis, I summarize the reasons for learning cursors into the following two points:
1. The existing system has some cursors, and our queries must be implemented through cursors.
2. As a backup method, when we have exhausted while loops, subqueries, temporary tables, table variables, self-built functions or other methods and cannot implement certain queries, use cursors to implement them.
The life cycle and implementation of cursors in T-SQL
In T-SQL, the life cycle of a cursor consists of 5 parts
1. Define a cursor
In T-SQL, defining a cursor can be very simple or relatively complex, depending on the parameters of the cursor. The parameter settings of the cursor depend on your understanding of the cursor principle.
A cursor can actually be understood as a pointer defined on a specific data set. We can control this pointer to traverse the data set, or just point to a specific row, so the cursor is defined On the data set starting with Select:
Cursors in T-SQL are defined in MSDN as follows:
DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]
[ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
[TYPE_WARNING]
FOR select_statement
[ FOR UPDATE [ OF column_name [ ,...n ] ] ]
[;]
It seems like a headache, right? Let’s talk about how to define a cursor in detail:
Cursors are divided into cursor types and cursor variables. For cursor variables, follow the definition method of T-SQL variables (what, don’t know the rules of T-SQL variable definition? Refer to my previous blog post). Cursor variables support two methods of assignment. , assignment when defining and assigning first and then defining. To define a cursor variable, add “@” before the cursor just like defining other local variables. Note that if you define a global cursor, only direct assignment at definition is supported, and you cannot add “@” in front of the cursor name. “@”, the two definition methods are as follows:
Let’s look at the parameters of the cursor definition:
Choose one between LOCAL and GLOBAL
LOCAL means that the lifetime of the cursor is only visible in batches or functions or stored procedures, while GLOBAL means that the cursor is globally valid for a specific connection as a context, for example:
If you do not specify a cursor scope, the default scope is GLOBAL
Choose one between FORWARD_ONLY and SCROLL
FORWARD_ONLY means that the cursor can only read from the beginning of the data set to the end of the data set. FETCH NEXT is the only option, and SCROLL supports the cursor to move in any direction or any position in the defined data set. ,As shown below:
Choose one of four STATIC KEYSET DYNAMIC and FAST_FORWARD
These four keywords are the relationship between the data in the table reflected in the data set where the cursor is located and the data read by the cursor.
STATIC means that when the cursor is created, a copy of the data set contained in the SELECT statement following the FOR will be created and stored in the tempdb database. Any changes to the data in the underlying table will not affect the contents of the cursor.
DYNAMIC is the completely opposite option to STATIC. When the underlying database changes, the content of the cursor is also reflected. In the next fetch, the data content will change accordingly.
KEYSET can be understood as a compromise between STATIC and DYNAMIC. Store the unique primary key of each row in the result set where the cursor is located in tempdb. When any row in the result set is changed or deleted, @@FETCH_STATUS will be -2, and KEYSET cannot detect newly added data.
FAST_FORWARD can be understood as an optimized version of FORWARD_ONLY. FORWARD_ONLY executes a static plan, while FAST_FORWARD chooses to use a dynamic plan or a static plan according to the situation. In most cases, FAST_FORWARD has slightly better performance than FORWARD_ONLY.
READ_ONLY SCROLL_LOCKS OPTIMISTIC Choose one of three READ_ONLY means that the declared cursor can only read data, and the cursor cannot perform any update operations
SCROLL_LOCKS is the other extreme, locking all data read into the cursor to prevent other programs from making changes to ensure the absolute success of the update
OPTIMISTIC is a relatively good choice. OPTIMISTIC does not lock any data. When data needs to be updated in the cursor, if the underlying table data is updated, the data update in the cursor will be unsuccessful. If the underlying table data is not updated, the table in the cursor will not be updated. Data can be updated
2. Open the cursor
After the cursor is defined, the cursor needs to be opened and used. There is only one simple line of code:
OPEN test_Cursor
Note that when the global cursor and local cursor variables have the same name, the local variable cursor will be opened by default.
3. Using cursors
The use of cursors is divided into two parts. One part is to operate the cursor’s pointing in the data set, and the other part is to operate part or all of the content of the row pointed by the cursor.
Only 6 movement options are supported, namely to the first row (FIRST), the last row (LAST), the next row (NEXT), the previous row (PRIOR), and jump directly to a certain row (ABSOLUTE(n)), relative to the current Jump a few lines (RELATIVE(n)), for example:
For cursors that do not specify the SCROLL option, only NEXT values are supported.
After the first step is completed, the value of this line is passed into the local variable through the INTO keyword:
For example, the following code:
Cursors are often used together with the global variable @@FETCH_STATUS and the WHILE loop to traverse the data set where the cursor is located, for example:
4. Close the cursor
After using the cursor, be sure to remember to close it. It only requires one line of code: CLOSE + cursor name
CLOSE test_Cursor
5. Release the cursor
When the cursor is no longer needed, releasing the cursor requires only one line of code: DEALLOCATE + cursor name
DEALLOCATE test_Cursor
Some optimization suggestions for cursors
- If you can do without a cursor, try not to use one.
- Be sure to close and release after use
- Try not to define cursors on large amounts of data
- Try not to use cursors to update data
- Try not to use insensitive, static and keyset parameters to define cursors
- If possible, try to use the FAST_FORWARD keyword to define the cursor
- If you only read data and only use the FETCH NEXT option when reading, it is best to use the FORWARD_ONLY parameter
Summary
This article discusses cursors from the basic concepts of cursors to their life cycle. Cursors are a very evil existence. Using cursors is often 2-3 times slower than using set-oriented methods. When the cursor is defined on a large amount of data, this ratio will increase. If possible, try to use while, subqueries, temporary tables, functions, table variables, etc. to replace cursors. Remember, cursors are always your last choice, not your first choice.
Cursors are evil!
The knowledge points of the article match the official knowledge files, and you can further learn relevant knowledge. MySQL entry skill treeSQL advanced skillsCTE and recursive query 70341 people are learning the system