Understanding Cursors
When starting out with ASP development many struggle just getting ADO to work, let along worrying about how or why it works. What results in code that is more expensive than is really required - in order to get the code working developers always use the most expensive cursors, since they will support all operations, rather than using the most appropriate cursor for the job. If you don't know much about setting the cursor type for an ADO Recordset object you might want to read this first.

What is a cursor?
This is a difficult concept to explain - but I'll give it a go :-) A relational DBMS (like SQL Server) returns the results of your query as a set based recordset. Basically the results are the subset of records from the table(s) that match your search criteria.

However most people, and applications, don't operate on the results as a set. Instead they work on the results in a sequential order. E.g. you edit the first record, update the database, move to the second record & edit it, update the database etc, or perhaps you just wish to write the results into a HTML table - you write the 1st record, then start a new row, write the 2nd record, start a new row etc.

In order to facilitate this movement one uses an ADO cursor. A cursor keeps track of which record you are currently at in the recordset. Different types of cursors allow different types of actions. Some cursors allow only movement forwards. Others allow movement forwards, backwards, and allow you to set "bookmarks" so you can quickly jump to a pre-set position in the recordset.

Which cursor type should I use?
As mentioned above, each cursor type allows different types of operations. One allows forward movement only (adOpenForwardOnly), whereas others allow movement backwards and forwards. The question that is asked is: "Why should I use a forwardonly cursor when I can get additional benefits from using the more sophisticated cursors?". The answer to the question is "performance". The more sophisticated the cursor the more work Jet, and ADO have to do in order to keep track of the records in the Recordset. For example, with a ForwardOnly cursor, once you have moved past a record, ADO & Jet can forget all about it (since you can't move backwards). However with an adOpenStatic cursor, ADO & Jet have to keep track of all the records since you can always move backwards.

My personal experience with various tests (and also my reading) is that an adOpenStatic cursor is about twice as expensive as an adOpenForwardOnly cursor. By this, I mean that performing operations like writing out the records in the recordset take twice as long using an adOpenStatic cursor compared to an adOpenForwardOnly cursor.

What types of cursors are there?
There are 4 types of cursors supported by ADO. We'll look at them all briefly now:

adOpenForwardOnly: This is the lightest (cheapest) cursor, and the default when opening a recordset. It allows only forwards movement. Only the most minimal information about the recordset is calculated by Jet (eg you can't even get a .recordCount of the total number of records in the recordset). If you try to move backwards using this cursor, the recordset is closed, and the query re-executed. Avoid doing this!

adOpenStatic: A static snap-shot of the records that match your search criteria are put into the recordset. You can scroll forwards and backwards, and set bookmarks. Changes made to the database by other users however are not visible - all you can see are the records that matched your search at the point in time when the query was executed

adOpenKeyset: A static snap-shot of the primary key values of the records that match your search criteria are put into the recordset. As you scroll backwards and forwards, the primary key value in the recordset is used to fetch the current data for that record from the database. This cursor thus allows you to see updates to the data made by other users, but it doesn't let you see new records that have been added by other users (because the primary key values for those records are not in your recordset).

adOpenDynamic: A dynamic snapshot of the database is maintained by OLEDB/ADO. All changes by other users to the underlying database are visible. Obviously this is the most sophisticated cursor, and thus is usually the most expensive. Because the data in the recordset is dynamic, attributes like AbsolutePosition and AbsolutePage can not be set. The adOpenDynamic cursor is not supported by the Jet OLEDB Provider (see below).

What cursors are supported by Jet?
This is a tricky question to answer. When you open the recordset you can request any of the 4 cursor types above. However, depending on the locktype you request, and the command type that you specify, the Jet OLEDB Provider may change the cursortype to something else behind your back! Jet never changes the locktype that you request however.

The table below shows the cursor type that you get, depending on the cursor and locktype you request:

There is one caveat to the above. If you set the CommandType to adCmdTableDirect you always get an adOpenKeyset cursor. The above values told for CommandTypes adCmdText and adCmdTable. Also be aware that using client-side cursors results in an adOpenStatic cursor no matter what cursor type you request. This behaviour is common to all OLEDB Providers, not just Jet. The default cursor location is server-side, so you don't have to worry about this unless you explicitly set the cursor location to client-side.

If you would like to see the code that generates the above results table click here.

Back to FAQ Listing