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:
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:
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.
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.
A server-side cursor uses server resources to implement a cursor and its set
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.