4GuysFromRolla.com - Recordset Cursors: Choose the Right Cursor for the Right Job

Windows Systems Administrator
Jupitermedia
US-CT-Darien

Published: Sunday, November 29, 1998

Recordset Cursors: Choose the Right Cursor for the Right Job

When you explicitly create a recordset object in ADO, you can specify what type of cursor you want it to employ. By explicitly creating a recordset, I mean that you issue the following command:

- continued -
Set rs = Server.CreateObject("ADODB.Recordset")

You can always implicitly create a recordset object, and most often developers do just that. For example, if we were to run the following code:

Dim rs, conn Set conn = Server.CreateObject("ADODB.Connection") 'Open the connection using an Application-level 'connection string conn.Open Application("Connection_String") Set rs = conn.Execute("SELECT * FROM pubs")

After the last line is executed, you have a bona fied recordset object in rs. It was created implicitly by ADO / ASP, and has a forward-only cursor. When you explicitly create a recordset object, however, you can choose what type of cursor you wish to employ by using the following line of code:

rs.CursorType = 'Enter cursor type here

The cursor type can be one of four types:

Forward-only

Static

Keyset

Dynamic

A forward-only cursor is the cursor with the least amount of overhead, thus leading to the best performance. It is also the default cursor for a recordset. It's only flaw is that it is uni-directional. You can only use commands which iterate through the recordset from front to end, such as MoveNext. You cannot use MovePrev, for example. If you try to issue a MovePrev command on a forward-only recordset you will get an error explaining that you cannot perform such an operation on a firehose cursor. (It is called a firehose cursor because like a hose, the stuff (records, in a recordset, water in a hose) is only going one way.) To use a forward-only cursor, you could type:

rs.CursorType = adOpenForwardOnly

Note: This article assumes you include adovbs.inc on each of your ASP pages. If you need the file it can be downloaded by clicking on the hyperlink.

The next type of cursor is a static cursor. This allows you to move in any direction, but it doesn't reflect any updates, deletions, or additions to the recordset while the user is viewing the recordset. It is as if a snapshot was taken of the data at the time it was requested. This recordset let's you use the full plethora of recordset traversal commands, such as MoveFirst, MovePrev, MoveLast, RecordCount, and others. You can use a static cursor by typing:

rs.CursorType = adOpenStatic

Dynamic and Keyset cursors allow you to see any changes which are made. While this is nice in a VisualBASIC application, it really doesn't apply to the web, since a request is made, and, like the static cursor, a snapshot of the information is brought back. There is no way to continuously update the data for the client without having the client refresh his screen (unless you delve into some of IE 4.x's new data features). Since this is the case for the web, you should never use Keyset or Dynamic cursors, since they hog up more resources than Static or forward-only. If you need to traverse through your recordset or need a count of your recordset, use a static cursor, else use a forward-only cursor

Using a forward-only cursor when only a forward iteration through a recordset is needed will save system resources and decrease query time. For that reason, be sure to choose the right cursor when you create your recordsets!

Happy Programming!