Using Cursor Locations

Cursor location is important, because you need to manage where cursors get their resources from (which would include CPU time, memory, and/or temporary storage space on either disk drives or in temporary database objects).

A cursor can be implemented at one of two general locations:

• Client-side cursors implement the cursor with resources on the local workstation (the "client machine").

• Server-side cursors implement the cursor with resources on the server machine

You can determine the cursor location of a result set by setting the CursorLocation property of an ADO Recordset or of an ADO Connection. The CursorLocation property has two useful values:

• 2 - adUseServer. The cursor will be server side. This is the default cursor location for ADO.

• 3 - adUseClient. The cursor will be client side.

If you set the CursorLocation property of a Connection object, the CursorLocation property of any Recordset created from that Connection will default to the value of the Connection's CursorLocation property, unless you explicitly set the Recordset's CursorLocation property to some other value.

NOTE - Obsolete CursorLocation Values Supported: For reasons of backward compatibility with earlier systems, the CursorLocation property also supports two obsolete values, adUseNone (whose value is 1) and adUseClientBatch (whose value is 3, or the same as adUseClient). These cursor location types are no longer used.

The following two sections discuss the consequences of cursor-location choice.

1. Client-Side Cursors

2. Server-Side Cursors

Client-Side Cursors

A client-side cursor uses local machine resources to implement a cursor and its set of records.

The advantages of client-side cursors are as follows:

• Because they run locally, they provide better performance when their result sets are a reasonable size.

• Client-side cursors generally provide better scalability, because their performance depends on each client, and not on the server. Therefore, client-side servers place less of a growing demand on the server as the number of a system's users increases.

The disadvantages of client-side cursors are as follows:

• When the rowset returned with the cursor is very large, the local workstation's resources may be "swamped" by the need to handle the high volume.

• Because a client-side cursor must bring all the data for its rowset over the network, larger result sets can increase network traffic.

Server-Side Cursors

A server-side cursor uses server resources to implement a cursor and its set of records.

The advantages of server-side cursors are as follows:

• Local workstation resources are never "swamped" by unexpectedly large rowsets.

• Because a server-side cursor does not transfer all the data in the rowset to the workstation, there is less network traffic with large rowsets when they are opened and less delay in opening them.

The disadvantages of server-side cursors are as follows:

• For smaller rowsets with a lot of activity performed by the application, server-side cursors do not perform as well, because each request to move the cursor and each response must travel over the network. It would be better to just transfer the smaller rowsets to the client to start with.

• As users are added to the system, the server receives a greater and greater resource demand as more and more concurrent users open server-side cursors. Server-side cursors therefore typically provide less scalability than client-side cursors.

NOTE - Client-Side and Static Cursors: When you set the CursorLocation property to Client-Side, the only available CursorType is Static.