SQL Story Extract (10) ———— the cursor should not be

Cursor
Overview of Cursors

I believe many Delphi programmers have written this code:

...

Begin

Mydataset.open;

Mydataset.frist;

While isn't (MYDATASET.BOF or mydataset.eof) do

Begin

...

End

Mydataset.close;

End

...

For a long time, we have been accustomed to using such code to do a line-by-row operation on the data returned by the database. Before opening a dataset with the client program's code, we think of it as an unordered collection. However, when needed, we can process the dataset directly on the server side as a row operation, which is the cursor.

The use of the cursor is similar to the previous Delphi code, usually has the following four steps:

One, declaration cursor: Declare Cursor, this process is not reflected in the previous code. However, we all know that before we use a dataset, we always define its properties, such as data sources, SQL statements, open methods, and so on. In cursors, the data source is generally not specified (because in the current database, the heterogeneous data source can also be read in MS SQL Server through the SQL language). But we're going to specify a dataset for it, and we can give it a different set of open options, such as whether to allow write operations, random reads, and so on. In general, the default cursors for the database system are read-only, one-way, and row-by-line.

Second, open cursor: Open Cursor, a Delphi (or other development platform) dataset component, after specifying a variety of necessary attributes, You must also use an open command (such as the previous mydataset.open) to open it, to get the dataset we need, for cursors, we also need an open command to open it in order to use.

III. Operational Data: This step typically involves moving the current cursor, reading the current data, and manipulating the code in three parts. A large portion of the cursor properties you set earlier is about what you can do with it. For example, for bidirectional cursors, we can move forward or backwards, and random cursors, we can even specify the position of the cursor in a random way, but the most common one is one-way, read-only. And for the current row of data, we can often by defining some variables to read, or move to the appropriate location to write operations, this is the same as the general development tools. As for the operation code, although this is not the strength of SQL, but the general database system also provides the basic process of coding capabilities, can let us complete the operation.

Closing the result set: Close Cursor things to finish, in Delphi, if you forget to close your open dataset, will bring a lot of trouble, and in the database system, if open a cursor did not close (think about it, cursor operation is to lock the data), If a lot of users are executing this problematic code ... So, in the standard syntax for cursors, there is a clear statement that closes the dataset and frees up the various resources it occupies. This is much more like a destructor in oo language than the Close method of the Delphi DataSet component.

In each of these steps, the SQL standard agrees with the corresponding implementation code. But the implementation of the individual DBMS platform is very similar. The problem is that the difference is enough to annoy people. So I'm not exactly writing out the implementation here. Readers can look at the Help files that they use with the system, and see how the database they are using is a cursor and what they have achieved.

An unreasonable existence.

We can see that there are quite a few differences between cursors and other parts of the SQL language. It is very complex to implement and operate, and is typically slower than collection operations because it is done on a row-by-line basis and completes the same functionality. How big is the gap? To give an extreme example: I have experimented with cursors to populate a table with row numbers, and the results were not completed for 12 hours, and the same operation was done in less than three seconds with the unequal joins mentioned in the previous article. I guarantee that not all situations will be so big, but it does exist. Especially when a large number of concurrent tasks exist, it is dangerous to lock in for a long time. Complexity and inefficiency, the biggest drawback of a cursor, is enough to allow us to have a cautious attitude towards it for just two points. Also, in general, operations that need to be performed with cursors can be done at the client (through the so-called host language, host Language).

Perhaps, in the extreme relationship model proponents, the cursor is an ugly existence. In a perfect, elegant system that manages information in a disordered set, why do we place a cursor that operates sequentially in an orderly manner? However, as in the Dragon Gun Chronicle, the sacred Paladin Temple of Ehtar City, but the darkest Black wizard Festin but jeans, in the vast rigorous relational database, there are such an alternative existence of cursors. It is the will of the gods that created the world of relations, and it has its own truth.

Existence is reasonable

The cursor has such a flaw, but it also has the existence value. First of all, when the need for orderly operation of the data set is very large, especially the final results of the operation is relatively small, if you want to send to the foreground to do, the network resources waste too much. Moreover, a large dataset is passed, and the host language does not necessarily support such a large data structure (such as the Delphi VCL container is much blamed on this), which limits our use of host language to expand system functions (such as MS SQL The server and InterBase could have written extension functions and extended stored procedures. In addition, interacting with the foreground is often less efficient if you want a large number of write-by-line writes. Cursors are really not a good method, but there is no better way, it is the best way. And then, in my experience, a script-written cursor is better than the maintainability and debugging of the two code that the host language compiles.

In the earthly world, there is no absolute darkness and light. The key is whether we make the right use of it.

Using cursors correctly

The cursor itself has no so-called right or wrong, but when it is used, we should think twice before we do it.

Many times, cursors may not be the only way you might think. I've seen too many cursor scripts that could have been done with more concise and efficient structured operations. As long as simple statements can achieve the same effect, do not use cursors. "Programmer" read a sentence: Simple is smart. This is the truth of software development.

The cursor is clearly read-only, one-way cursor speed is the fastest, and it is not easy to cause deadlock, use it as much as possible.

Creating an appropriate index on the table used by the cursor is more efficient than a typical SQL statement, especially a cursor that performs a write operation.

The result set of the cursor operation, to be as small as possible.

If there is a large number of operations in the cursor code, consider whether to spread it out to other servers or clients.

The cursor code should be fully tested and validated, and then put into use, especially the optimizer and stability. The system cannot be trusted in this respect. For example, if you write a cursor and add one variable to each row, the system will never actively optimize it to count (*).

Some systems can hold a cursor opened by the current transaction to a later transaction until it is turned off. But it's best not to use it casually. This feature certainly looks cool, but abusing it can cause endless problems. Do you really need this kind of function?

When appropriate, write it as an extended stored procedure or extended storage function, linked into the database system in the form of binary code. The disadvantage of this is the loss of flexibility, in exchange for increased efficiency.

Report:

No stored procedures, triggers, or even cursors can be built until you have entered multiple, batch-executed statements in the InterBase isql. Until one day, attention was paid to it, and it was found that this was done in isql:

Set term^;

...

^

...

^

...

Set term;^

Starting with the first line of set term^, isql will send a group of statements separated by ^ to the background execution until set term;^. A bit like MS SQL Server's Query Analyzer's "go". In this way, we can easily write scripts with isql.