Introduction
There are two reasons why I've written this article. Firstly there are a number of people who seem to be unaware of the consequences of specifying adUseClient as a cursor location on the resultant cursor type. Secondly, there are a number of people who suggest using adUseClient in order to get an accurate .RecordCount from an ADO Recordset object.

Whilst it is true that specifying adUseClient will return an accurate recordcount (I've written the same here), it appears that some people are missing the point as to why the record count is accurate (because the cursor type becomes adOpenStatic). As such they believe that there is no performance penalty in using adUseClient (in that an adOpenForwardOnly cursor can still be used).

In fact the reverse is true. Specifying adUseClient results in two possible performance penalties. Firstly, the cursor type is set to adOpenStatic, which is about twice as expensive as adOpenForwardOnly. Secondly, in situtations where the DB and web servers are physically separate, additional network traffic is generated as the OLEDB Cursor Service located on the web server is now managing the cursor, not the DBMS.

The following code can be used to connect to any database you wish (adjust the connection string and table name in the Main routine). It will output the requested and actual cursor types. The code below was tested against an SQL Server 2000 database, and returned adOpenStatic as the actual cursor type for every requested cursor type.

<%
Option Explicit
Response.Buffer = True

Call Main()

Sub Main

   Dim strConnect ' As String: DB Connection String
   Dim objConn ' As ADODB.Connection
   Dim strTableName ' As DB table to open
   Dim arrCursorTypes(3) ' Array of cursor types to use
   Dim i ' As array counter
   Dim objRS ' AS ADODB.Recordset

   strConnect = _
      "Provider=SQLOLEDB.1;" & _
      "Integrated Security=SSPI;" & _
      "Persist Security Info=False;" & _
      "Initial Catalog=Pubs;" & _
      "Data Source=Test;"

   strTableName = "Authors"

   arrCursorTypes(0) = adOpenForwardOnly
   arrCursorTypes(1) = adOpenStatic
   arrCursorTypes(2) = adOpenKeyset
   arrCursorTypes(3) = adOpenDynamic

   ' Open a connection
   Call subDBConnOpen(objConn, strConnect)

   ' For each cursor type, open a recordset requesting the cursor type
   ' then write out the requested and actual cursor types
   ' the close the recordset
   For i = 0 to UBound(arrCursorTypes)
      Call subDBRSOpen(objRS, strTableName, objConn, arrCursorTypes(i))
      Call subDBRSPropertiesWrite(objRS, arrCursorTypes(i))
      Call subADOClose(objRS)
   Next

   ' Close connection
   Call subADOClose(objConn)

End Sub

' These are all the supporting routines

'--------------------------------------------------
' --- Opens an ADO Recordset using supplied
' --- connection object, source, and cursor type.
' --- For this page, cursor location is set to
' --- adUseClient.
' --- Accepts objRS as Recordset object
' --- Accepts strTableName as source
' --- Accepts objConn as connection object
' --- Accepts cursortype as ADO constant
'--------------------------------------------------
Sub subDBRSOpen( _
   ByRef objRS, _
   ByVal strSource, _
   ByRef objConn, _
   ByVal varCursorType _
   )

   Set objRS = Server.CreateObject("ADODB.Recordset")
   objRS.CursorLocation = adUseClient
   objRS.CursorType = varCursorType
   objRS.Open strSource, objConn,,adLockReadOnly, adCmdTable

End Sub

'--------------------------------------------------
' --- Write's ADO recordset's cursortype
' --- actual -vs- requested
'--------------------------------------------------
Sub subDBRSPropertiesWrite( _
   ByRef objRS, _
   ByVal constRequestedCursorType _
   )

   With Response
      .Write("<p>Requested Cursor Type: " & fncGetPrettyCursorName(constRequestedCursorType) & "<br />" & vbCrLf)
      .Write("Actual Cursor Type: " & fncGetPrettyCursorName(objRS.CursorType) & "</p>" & vbCrLf)
   End With

End Sub

'--------------------------------------------------
'--- Returns the named constant for a given ADO CursorType
'--------------------------------------------------
Function fncGetPrettyCursorName( _
   ByVal constCursorType _
   )

   Select Case constCursorType
      Case 0
         fncGetPrettyCursorName = "adOpenForwardOnly"
      Case 1
         fncGetPrettyCursorName = "adOpenKeyset"
      Case 2
         fncGetPrettyCursorName = "adOpenDynamic"
      Case 3
         fncGetPrettyCursorName = "adOpenStatic"
      Case Else
         fncGetPrettyCursorName = "unknown cursor type!"
   End Select

End Function

'--------------------------------------------------
'--- Opens an ADO Connection using
'--- supplied params
'--- Accepts objConn as object to open
'--- Accepts strConnect as ADO connection string
'--------------------------------------------------
Sub subDBConnOpen( _
   ByRef objConn, _
   ByVal strConnect _
   )

   Set objConn = Server.CreateObject("ADODB.Connection")
   objConn.Open strConnect

End Sub

'--------------------------------------------------
'--- Closes supplied ADO object
'--- Accepts objToClose as object to close
'--------------------------------------------------
Sub subADOClose( _
   ByRef objToClose _
   )

   On Error Resume Next
   objToClose.Close
   Set objToClose = Nothing

End Sub
%<

Back to Experiments Listing