How ADO Uses Cursors

The answer to an SQL deficiency

Because Structured Query Language (SQL) is a set-oriented language, SQL Server performs an operation on a row only if that row is a member of the set defined by the WHERE clause in the SELECT, UPDATE, or DELETE statement. A set-oriented language lets you apply a change to all the rows as a group rather than issuing a separate command for each row.

However, the problem with a set-oriented language is that it lacks a concept of order or individuality for the items in the set. An item has only one property—membership in the set. Therefore, you cannot move from the first row in the set to the last row, nor can you retrieve information from a particular row without executing another query that targets that specific row.

Cursors work around this deficiency in SQL by artificially imposing order and individuality on the rows in the rowset that the SQL SELECT statement returns. In this article, I will explain how ADO uses cursors as a storage and access mechanism, and how you can choose the best cursor for your application.

Why ADO Uses Cursors

When your application retrieves rows of data from SQL Server, it needs a place to store the rowset while it processes the information. An ADO Recordset object encapsulates the data and the operations allowed on that data. The Recordset object uses a cursor as a mechanism to organize the temporary storage. The cursor is similar to a dynamic array stored in memory, and the Recordset object is your interface to that array.

Within ADO, cursors have three functions. First, the cursor location determines where to store the rowset while the cursor is open. Second, the cursor type determines movement within the cursor and whether the rowset will reflect users' changes. Third, the cursor's locking type specifies how SQL Server will lock the rows on the server when you want to make changes.

Later in this article, I show you how these three functions interact to affect the application's functionality and the load the application places on the server. First, let's look at each function and its effect on the cursor.

Cursor Location

The cursor location determines whether ADO or SQL Server manages the cursor. The MSDASQL and SQLOLEDB providers manage rowset storage on the client side. SQL Server retrieves the rows from the table and uses either ODBC drivers to send them to MSDASQL or uses the OLEDB interface to send them to SQLOLEDB. In either case, the client-side database drivers allocate virtual memory to hold the rows.

With SQL Server, server-side cursors take up space in the TempDB database. The MSDASQL and SQLOLEDB providers call special functions to tell SQL Server which rows to retrieve and which type of cursor to open. Then, instead of sending the entire rowset to the client, SQL Server stores it in TempDB. The database drivers then retrieve individual rows at the client application's request.

Cursor Types

ADO supports four types of cursors: static, dynamic, keyset, and forward-only. These cursor types vary in how the cursor responds to changes in its row membership and in what directions you can move through the rowset. Table 1 lists each cursor type and its attributes when the data source is SQL Server.

Static Cursors. Static cursors are so named because additions and deletions of rows don't change the list of rows. Furthermore, changes to existing records don't appear. Any change the cursor owner makes through the cursor appears immediately, but the static cursor ignores only modifications by other users until the application refreshes the cursor. In general, you can think of a client-hosted, static cursor as a local copy of the table that's isolated from the rest of the database system.

Client-hosted and server-hosted static cursors let you use the MoveFirst, MoveNext, MovePrevious, MoveLast, and Move methods of the Recordset object to move to any row in the cursor. Also, both cursors let you move to a specific row by setting the Bookmark property on the Recordset. Bookmarks are internal identifiers that the ADO cursor engine maintains and that provide a unique address for each row in the cursor. To move to a specific row, you read the Bookmark property and store the value in an application variable. If you set the Bookmark property to the value stored in the variable, the cursor position moves back to the row. You can use Bookmarks to create a list of specific rows.

In addition, client-hosted static cursors let you move to a specific row by setting the AbsolutePosition property equal to the row number. The valid values for the property are 1 to the number of rows stored in the Rowcount property. An important consideration when you use the AbsolutePosition property is that a row's position within the rowset may change depending on inserts and deletes. For example, if you delete row 4, then row 5 becomes the new row 4. Therefore, SQL Server documentation recommends using Bookmarks instead of row numbers, but absolute positioning works well for read-only cursors.

Forward-Only Cursors. Forward-only cursors are similar to static cursors except that forward-only cursors let you scan only from the first to the last rows in the cursor. You can update rows, insert new rows, and delete rows, but you can't move backward. Only the MoveNext method works for the forward-only cursor.

In SQL Server 7.0, forward-only cursors are a special case. Whereas most database APIs treat forward-only cursors as a specific kind of cursor, SQL Server 7.0 considers them a kind of cursor behavior, meaning that the cursor defines how you can scroll through the data. Thus, when you ask SQL Server 7.0 to create a server-side, forward-only cursor, you get a dynamic cursor that supports scrolling in only one direction.

Keyset Cursors. With keyset cursors, the row membership and row order are fixed when you open the cursor. As with a static cursor, your application can move back and forth between rows. Unlike static cursors, however, keyset cursors let you see changes other programs make and changes your program makes. Remember that client-hosted cursors are essentially local copies of the data; therefore, a client-hosted keyset cursor isn't useful. In my tests, ADO always changes these cursors into client-hosted static cursors.

Keyset cursors present three special cases. First, when a user deletes a row, SQL Server marks the row as deleted and it becomes inaccessible. Second, if a user changes a row so that it no longer matches the cursor's WHERE clause criteria, SQL Server in effect removes the row from the cursor as if the user had deleted it. The row still exists in the table, but it is inaccessible. Third, if another user inserts a row into the table, SQL Server doesn't add it to the cursor, but if you add a row through the cursor, it appears at the end of the cursor.

Dynamic Cursors. If your application requires immediate access to all changes, regardless of who makes the changes, you need to use dynamic cursors. Dynamic cursors let your application move in any direction through the cursor, respond immediately to changes in rowset membership, and show all users' changes to all rows. Choose dynamic cursors if multiple users insert, update, and delete rows in the database at the same time.

Dynamic cursors are flexible, but they don't support absolute positioning. Because dynamic cursors respond to all membership changes, they don't provide a way for you to identify, for example, the 10th row in the cursor. Moreover, dynamic cursors don't support bookmarks because the cursor can't guarantee that the unique identifier stored on the client will be valid the next time the application wants to use that row. SQL Server maintains one row in its buffer, and it re-executes part of the query to find the next row. Thus, dynamic cursors use more server resources than other cursors. Although dynamic cursors are powerful and extremely flexible, use them only when you need to.

Locking Choices

After you choose a cursor type, you need to choose how ADO will lock a row when you make a change. Table 2 shows the options available for each type of cursor when you use the SQLOLEDB or MSDASQL providers. In making this choice, you aren't specifying the kind of lock; you're specifying when SQL Server locks the row. When you tell ADO to make an update, SQL Server places an exclusive lock on the page (or the row in SQL Server 7.0).

Read-Only Locking. The read-only locking option doesn't use a lock at all. This option tells ADO that you won't make any changes to the rows in the cursor. At most, SQL Server will place shared locks on the rows as it reads them and release the locks after it populates the cursor. Because applications don't usually change data through a cursor, read-only locking is the default setting.

Pessimistic Locking. Pessimistic locking assumes that your changes will collide with another user's. This option tells ADO to get an exclusive lock on the row when you make any change to any column in the record. The problem the pessimistic locking option presents is that the row stays locked until you commit your changes by calling the Update method. If your update occurs in the middle of a transaction, then the lock remains until the transaction ends. Therefore, don't wait for user input after you make changes to the row, especially if you're using SQL Server 6.5, which locks the entire page and not just the row. Instead, make your changes in the fastest possible way, then call the Update method to update the row and release the lock.

Optimistic Locking. To offset the problems of pessimistic locking, ADO offers the optimistic locking options, Optimistic and BatchOptimistic. These options assume that collisions are unlikely; therefore, ADO gets the exclusive lock only after you call the Update or UpdateBatch method. The Optimistic option lets you work with one row at a time, whereas the BatchOptimistic option lets you make multiple updates within the cursor, then save them all at once. When you call the UpdateBatch method, ADO tries to update each changed row as a separate, independent update.

No implied transaction occurs with the BatchOptimistic option. Some updates can succeed; others fail. If a row's update fails for any reason, ADO sets the Recordset's Status property for that row to a value that identifies the problem, then continues processing. When the UpdateBatch method finishes, you can set the Recordset's Filter property to adFilterConflictingRecords, which forces the Recordset to show only the records whose updates failed.

When a Cursor Is Not a Cursor

In SQL Server 7.0, a cursor isn't a cursor if you ask for a server-hosted, forward-only, read-only cursor. This type of cursor, called a firehose cursor, prompts SQL Server 7.0 to use its Default Result Set to stream records to the client. SQL Server 7.0 fills network packets with rows as soon as it can and sends the network packet to the client. The client's networking software buffers the packets until ADO is ready to present the rows to the application. SQL Server uses the networking subsystem as a data buffer so that it can send rows as fast as possible.

SQL Server also uses Default Result Sets when you execute a stored procedure that returns records. The Command object and the Recordset object create a forward-only, read-only recordset. The Execute method of the Connection object always returns a forward-only, read-only recordset, and because the Connection object's default is to use server-hosted cursors, the commands that the Execute method sends will always return Default Result Sets.

The firehose cursor approach assumes that the client will retrieve all rows from the cursor and won't change any of them; therefore, SQL Server can send all rows at once. This approach eliminates the roundtrips across the network that ADO makes when it needs to fetch another row, which means this kind of cursor uses the least amount of network and server-side resources and uses a minimal amount of virtual memory on the client.

How to Choose a Cursor

Your choice of cursor depends on how your application will use the data. Table 3 shows which cursor types are available for client-hosted and server-hosted cursors. When you need data to fill a listbox or to create a report, choose the firehose cursor. In this case, the firehose cursor will yield the best performance because your application will receive records as soon as SQL Server 7.0 can fill a network packet. An additional benefit of the firehose cursor is that your application will be able to process records and receive new records simultaneously because the networking subsystems in Windows NT and Windows 95/98 will receive network packets in parallel with your application's execution.

The one limitation of the firehose cursor is that SQL Server allows only one operation on the connection at a time. Because SQL Server 7.0 streams records with no fetch commands from the client, it imposes the restriction that any new operations must wait until all the rows in the firehose cursor have been sent to the client. Therefore, you can't execute an UPDATE, INSERT, or DELETE while you are reading rows from a firehose cursor. No other cursor has this restriction.

Another common type of application is one with a data-entry form that contains data from only one record and is updated when the user moves to another record. For this kind of application, you can use either a client-hosted static cursor with optimistic locking or a server-hosted keyset cursor with pessimistic locking. The benefit of pessimistic locking is that SQL Server guarantees that transactions involving the record will succeed. Transactions fail only if deadlocks occur.

If you use pessimistic locking, write your code so that records are locked no longer than necessary. The worst-case scenario is one in which a user opens a record and makes a change but doesn't save the entire record. Then the user goes to lunch. You can use timers and automatic timeouts to handle such situations, but the best practice is to use pessimistic locking only when a record needs to be isolated from actions by other users. In all other cases, use optimistic locking.

Likewise, if you use data-bound objects in Visual Basic 6.0, don't use pessimistic locking because ADO will lock the record the moment you change one byte. Use optimistic locking for data-bound objects. The best choice is a client-hosted static cursor, although server-hosted keyset cursors work well for applications that share data changes with the other users.

Similarly, for applications that use grid controls, a client-hosted static cursor or a server-hosted keyset cursor works best. Both of these cursors let the application move freely within the cursor; therefore, the choice depends on how many rows the user will visit and how many rows will incur changes. If the user will visit at least 50 percent of rows, it makes sense to cache the data on the local machine. If the user will jump around within the cursor, the keyset cursor offers the advantage of retrieving the complete row contents only when the application requests them. The keyset cursor also retrieves the most up-to-date version of the row, which is useful when the user must make decisions based on information that multiple concurrent users can modify.

For situations in which a user can make many changes and then decide to cancel all of them at once (e.g., by clicking on the Cancel button of an order entry form), BatchOptimistic locking restores data integrity and consistency. SQL Server doesn't send the changes to the server until the program calls the UpdateBatch method. Therefore, you can simply close the Recordset to discard changes. BatchOptimistic locking also eliminates the network roundtrips that occur with Optimistic locking when users make changes to one record, then move to another. For example, in a master-detail application such as order entry, users often modify many rows at the same time. Those modifications are single-row updates, but they are logically grouped into one entity. BatchOptimistic locking lets the user make all the entries at one time and submit them as a single operation. This capability is especially important with data-bound controls.

Choosing the right cursor comes down to two factors: where will the cursor data reside, and does the application need to see changes in the rowset while the cursor is open? After you answer those questions, the choice of cursor is easy. Figure 1 lists some of the trade-offs between client-hosted and server-hosted cursors.

To see options available for the SQLOLEDB and MSDASQL providers, go to http://www.MCSDonLine .com, which posts a VB application that demonstrates every possible combination of cursor location, cursor type, and locking type. Knowing which types of cursors are available will help you in the decision-making process. And if you use ADO, you can change your mind about your cursor choice without having to change a lot of code.