Ado cursor Basics

I

1. What is the cursor?

The cursor, also known as the cursor. In summary, it is based on records.

In the past, relational databases were not widely used as they are now. At that time, most people used small database software such as DBASE. This type of database should be exactly the data file management software. They are record-oriented.

However, this method may be more in line with people's habits. For example, when we look for a number in a phone book and find an archive in a student's archive, it all comes down to a number. One archive is a record. In real life, when we look for a certain item in a table, we may scan it row by hand to help us find the desired record. For databases, This is the cursor model. Therefore, you can imagine that a table is a table in a database, and our hand is like a cursor.

Therefore, when you use statements such as. movenext and. movelast, you feel that it is no longer necessary.

Now, you know what the cursor is. The cursor is the 'positioning system' of the data '.

The 'positioning System' has two kinds of rough points: the server cursor and the customer cursor. Corresponds to cursorlocation In ADO. For example:

Set rs = server. Createobject ("ADODB. recordset ")
Rs. cursorlocation = aduseserver by default, use the server cursor
.....
Rs. cursorlocation = aduseclient use customer cursor

2. What is the server cursor?

Or the API server cursor.

Suppose you want to query books related to programming languages and write them into SQL statements: Select book_id, book_title from books where book_catalog = 'programmatically 'order by book_title, And you specify to use the server cursor.

This statement is sent to the server. The server searches for the data and puts the set of records that meet the query conditions into a temporary table (for some cursor types. Every time you perform the. movenext operation, the server will send a record to the client's buffer before you can use it.

3. What is cachesize?

In the preceding example, we assume that there are 100 records that meet the query conditions. That is to say, to traverse the result set using the. movenext method, we need to interact with the server for 100 times. We can reduce the communication between customers and servers by setting cachesize. The above example is actually about cachesize = 1, which is the default value.

Assume that cachesize is 4. When the recordset object is opened, the server sends four records to the client. The first four. movenext operations actually get data in the customer buffer. When 5th. movenext operations are performed, the server sends the next four records. Therefore, network communication between the customer and the server is reduced.

Does it mean that the larger the cachesize, the better? Never take it for granted. Everything has two sides, and so does cachesize. The client requests data and the server sends data. This process is a bit like traffic management. High cachesize may cause traffic congestion or even data loss (for example, when the cachesize is greater than the client buffer ). Different applications have different values.

In addition, it should be pointed out that using any form of optical mark is not the most effective way to access data, and cachesize is sometimes not a bottleneck, convert the program that uses the cursor to the result set as much as possible, and the performance will be improved a lot.

3. What is the customer's cursor?

Since the cursor is the 'positioning system' of the data, it can be done on the client.

The customer cursor has been generated for a long time. It was originally designed to make up for the server's shortcomings (some database engines do not support the cursor ). With the appearance of the server cursor, the client cursor seems to be outdated, but it is still the same sentence: Everything has two sides. on the Internet, the connection to the database is not permanent, using the customer cursor can give us the same functionality as using the server cursor.

When the cursorloction attribute is set to aduseclient, Microsoft's cursor service creates a recordset object and retrieves all query results from the server using a forward/read-only cursor, and stored in the customer buffer. When an application requests data through ado, the cursor service checks data from the customer's buffer zone. This method is useful when connecting to a remote server. It improves the performance of applications. If the database you access is a Jet Database (ACCESS) and is local, using the client cursor will not only improve performance, but also degrade performance. At this time, the data will be cached twice, database once, and cursor service once.

If you consider the application features, the customer cursor feature is perfect, and it supports operations that cannot be completed by some databases (depending on the database situation ).

4. What is disconnected recordset?

With the client cursor, We can disconnect from the database and release the connection object. Such a result set is disconnected recordset. Example:

Dim C as new ADODB. Connection
Dim R as new ADODB. recordset
On Error goto Handler

C. connectionstring = connectstr
C. cursorlocation = aduseclient
C. Open
Set R. activeconnection = C
R. Open sqltext, adopenkeyset, adlockbatchoptimistic,-1
Set R. activeconnection = nothing 'This disconnects the recordset.
C. Close
Set c = nothing
......
......
'Recordset is now in disconnected state; do something with it.
R. Close
Set R = nothing
......

 

II

In the cursor base (1), it mainly describes what is the cursor, what is the server cursor, and what is the customer cursor. We also compare the cursor to a hand, which is used to locate a record.

The cursor is not as flexible as the hand, and more complex. Based on different needs, the cursor is divided into static, keyset, dynamic, and forward cursor ). At the end of the article, we will also introduce the concept of the default result set.

1. Static cursor

The static cursor generates a temporary copy of the query result and stores it in tempdb. All your operations on the result set are actually performed on a private, temporary data copy, the members of this result set are fixed, and any modifications made to the base table by others are not reflected in real time.

Creating a temporary table with a static cursor on the server is costly. We know that tempdb is often used for complex data retrieval. If the result set is too large, it will affect the efficiency of data query.

2. keyset cursor

Unlike static cursors, a set cursor only stores the key value of each row that uniquely identifies a result set in tempdb-a key set (for example, an index ).

Suppose we want to query such a statement "select cust_name from MERs where cust_id> 100", assuming that user 101,200,300 meets the query conditions. After that, these key values are used for each extraction operation. In other words, the later. movenext operation will allow the server to search again based on the keyset, that is, "select cust_name from MERs where cust_id in (101,200,300 )". Even if other users enter 400, it will not appear in the result set. Modifications made based on the key set can be seen.

For the keyset cursor, the table used by the cursor must have a unique index. If this condition is not met, the cursor is converted to a static cursor.

3. Dynamic Cursor

In this type of cursor, the SELECT statement is executed once every time records are extracted. Unlike the keyset cursor, it executes the original SELECT statement, that is, "select cust_name from customers where cust_id> 100 ". This means that the members of the Dynamic Cursor are not fixed. The modifications you make within the cursor or the modifications made by others will be reflected in the next extraction.

Many people ask: why does recordset. recordcount return-1? (-1 indicates that you do not know how many records there are.) In fact, it is not difficult to draw a conclusion. The static cursor and the key set cursor are fixed. Naturally, you can know how many records are there, but the dynamic cursor is opposite. It cannot return a variable value at any time.

4. Forward cursor

As the name suggests, only the cursor that scrolls forward. This is the default cursor of ADO. Generally, data is processed from start to end, that is, only one direction is followed, so each row will not be extracted again. Forward cursor is the fastest cursor.

5. Default result set

The default result set is the result set that is not returned as a cursor.

Its other name is 'firehose', which reflects the way the default result set works: as long as the client continuously processes the returned records, so that the network buffer has space to send more results, the server will continue to send records to the client. The client does not send requests to the server. The server only continuously sends data to the network.

Although we regard the default result set as a forward/read-only cursor, it is just a name, which is indeed different from the actual cursor. We can use the word cursor, but it should be clear: From the Perspective of SQL Server, the default result set is not a cursor, it does not involve the server's cursor service.

The efficiency of the default result set is usually higher than that of the cursor.