Server Side Cursors and ADO Cursor Types

By Bill Graziano on 11 November 2002 | Tags: Performance Tuning

This article discusses some common ADO properties (cursor location, cursor type and lock type) and their effect on performance. It's mainly concerned with eliminating server-side cursors. These are identified in Profiler traces by sp_cursorfetch statements.

Lately I've been doing a lot of performance tuning. At a number of different clients I've seen the same type of results in Profiler. They look something like this:

declare @P1 int
set @P1=180150000
declare @P2 int
set @P2=8
declare @P3 int
set @P3=1
declare @P4 int
set @P4=3
exec sp_cursoropen @P1 output, 
 N'select top 3 * from authors', 
 @P2 output, 
 @P3 output, 
 @P4 output
select @P1, @P2, @P3, @P4
go
exec sp_cursorfetch 180150000, 16, 1, 1
go
exec sp_cursorfetch 180150000, 16, 2, 1
go
exec sp_cursorfetch 180150000, 16, 3, 1
go
exec sp_cursorfetch 180150000, 16, 4, 1
go
exec sp_cursorclose 180150000
go

This trace was generated using the default Profiler template. Each of the statements between the GO's is a round trip to SQL Server. This type of result is called a server-side cursor and it's generated by ADO. It came from the following SELECT statement:

select top 3 * 
from authors

That works out to six round trips in order to return three rows of data. That's one round trip to setup the cursor, three round trips to each return one row, one round trip to return an empty set (i.e. no more data) and a last round trip to close the cursor. The more rows there are the more round trips to the server. One article I read called this a "badminton" cursor since it was bouncing back and forth between the client and SQL Server.

The alternative to a server-side cursor is a client side cursor. In this case the trace would just show the SQL select statement and return all the rows to the client in one round trip.

I generated the server side cursor using this VBScript snippet:

sConnString = "Provider=sqloledb; Data Source=L20; 
    Initial Catalog=pubs; User Id=sa; Password=yourpassword"

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.CursorLocation = adUseServer
objConn.open sConnString
sql = "select top 3 * from authors"

set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open SQL, objConn, adOpenStatic, adLockOptimistic

Do Until objRS.EOF = True
   Response.Write objRS("au_lname")
   objRS.MoveNext
Loop

objRs.Close
Set objRS = nothing
objConn.close
Set objConn = nothing

There are three parameters that control whether you recieve a server side cursor or a client side cursor. And they interact with each other in intereting ways. The first is the CursorLocation parameter of the Connection object. The two options are adUseServer and adUseClient. The ADO documentation says that adUseServer is default.

The second is the CursorType property of the RecordSet. It's options are adOpenForwardOnly, adOpenKeyset, adOpenDynamic and adOpenStatic. The default opotion is forward only. The simplest type of cursor is the static cursor (adOpenStatic). It generates a static list of rows at the point in time it is executed. You can move forward and backward through result sets. If another user changes a record in your cursor you won't see the change. Forward only cursors (adOpenForwardOnly) are static cursors but you can only go forward through the rows. Dynamic cursors (adOpenDynamic) show changes that other users make and allow you move forward and backward. Keyset cursors (adOpenKeyset) are like dynamic cursors but you can't see records that are added by other users and deleted records in not accessible.

The last option is the LockType. It's options are adLockReadOnly, adLockPessimistic, adLockOptimistic and adLockBatchOptimistic. There's also an option for an unspecified lock type. For the purposes of locking we'll consider either read-only or read/write. The default is read-only.

Looking back at my first example, I used server-side, static, read/write cursor. That generated one round trip to SQL Server per row returned with three extra round trips for housekeeping. Changing that same cursor to a server-side, read-only, forward only cursor generated one round trip to return all the data from the query. The relevant portion of that query looked like this:

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.CursorLocation = adUseServer
objConn.open sConnString
sql = "select top 3 * from authors"

set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open SQL, objConn, adOpenForwardOnly, adLockReadOnly

After researching the combinations of the three parameters I found that all the combinations with a client-side cursor generated a single call to return their result sets. The only server-side combination that used a single round trip to return the results was the forward-only, read-only cursor. This is commonly called the "firehose" cursor. While I didn't have time to test it, I'd suspect that client-side cursors aren't as sensitive to data changes as server-side cursors.

One of the unexpected downside of server-side cursors is temporary tables. The sp_cursor stored procedures typically use temporary tables to generate their results. In some cases I've seen one temporary table created and destroyed per record returned by the cursor. This generated significant contention in tempdb and was forcing the system to single-thread through object creation in tempdb. This occurred in SQL Server 7 and I've been unable to test in SQL Server 2000 under load.

In summary, to minimize round trips to SQL Server use client-side cursors or server-side, read-only, forward-only (aka "firehose") cursors. They can give you a significant performance boost.