Cursor Types

I found a very nice document on web (did not remember the exact source) describing Cursor Types in details. So sharing the same with you all.

Cursor Types

In addition to the two cursor locations, there are four cursor types, three of which are supported under Connector/ODBC:
• adOpenStatic (Client-Side)
• adOpenForwardOnly (Server-Side)
• adOpenDynamic (Server-Side)
The different cursor types support different functionality and features, and I will now discuss each one in detail. The fourth cursor type, adOpenKeySet, is not currently supported by MySQL / MyODBC.
adOpenStatic
The static cursor is the only cursor type that is currently available when using adUseClient as your cursor location. With a static cursor, the server will send the result set to the client, after which there will be no further communication from the server to the client. The client may communicate with the server to send changes back to the server. This makes the static cursor more resource-intensive for the client and less resource-intensive for the server, as the result set is stored in the client's memory instead of the server's.
If a different client makes changes to the underlying data after the query results are sent, the original client will receive no notification of the change. A static cursor is bi-directional, meaning that your application can move forwards and backwards through the recordset. The following methods are available to a recordset using a static cursor and the adLockOptimistic lock type (more on lock types later):
• AddNew
• Delete
• Find
• MoveFirst
• MovePrevious • MoveNext
• MoveLast
• Resync
• Update
• UpdateBatch
The static cursor will also show an accurate value for the RecordCount property of your recordset, and supports the getchunk and appendchunk methods for dealing with BLOB data. If you are having trouble with either of these problems, explicitly setting your connection's cursorlocation to adUseClient should solve them.
One handy feature of the static cursor is the ability to fetch data asynchronously. When data is fetched asynchronously., a separate thread is started to handle row retrieval, and your VB application can begin processing returned rows immediately. An in depth article on asynchronous data fetching is pending, but to activate this feature, simple use the adFetchAsync option during your recordset.open method call.
If you specify any cursor type other than adOpenStatic when opening a recordset with an adUseClient cursor location, it will be automatically converted to a static cursor.
adOpenForwardOnly
The adForwardOnly cursor type is the fastest performing cursortype, and also the most limited. The forward-only cursor does not support the RecordCount property, and does not support the MovePrevious methods of the recordset object.
The most efficient way to access data for display to the screen out output to a file is to use a adOpenForwardOnly cursor with a adLockReadOnly lock type when opening a recordset. This combination is often referred to as a Firehose Cursor. A firehose cursor bypasses a lot of handling code between the client and server and allows for very fast data access when moving sequentially through the resulting rows.
The following recordset methods are supported when using a forward-only cursor with an optimistic lock:
• AddNew
• Delete
• Find
• Update
• UpdateBatch
In addition, the forward-only cursor type supports non-caching queries. While an asynchronous query allows data to be worked on immediately, it offers no memory benefits when accessing large resultsets, as all rows eventually wind up in memory, taxing system resources when accessing a large number of rows, or a medium number of rows when BLOB data is involved.
With MySQL and Connector/ODBC, we can specify option 1048576 in our connection string or check off the option "Don't Cache Results" in the ODBC manager in order to specify to the ODBC driver that it should only retrieve one row at a time from the server. With this option set, memory usage on the client is limited as only one row at a time is stored in memory. With every call to the recordset's MoveNext method, the previous row is discarded and the next row is queried from the server.
adOpenDynamic
While the forward-only cursor is the most efficient of the cursor types, the dynamic cursor, specified but adOpenDynamic, is the least efficient. Because of it's inefficiency, dynamic cursor support must be manually activated by using option 32 in your connection string, or by checking "Enable Dynamic Cursor" in the ODBC manager. Without this option enabled, any cursortype other than forward-only with be automatically converted to a static cursor, with it enabled, all cursor types other than forward-only will be converted to dynamic.
Why is a dynamic cursor so slow? As there is no native support for dynamic, server-side cursors in MySQL, every call to a row-moving method(MoveNext, MovePrevious, etc.) results in the Connector/ODBC driver converting your method call to a SQL query, posting the query, and returning the resulting row. This also means that for a dynamic cursor to work properly, your underlying table needs a primary key column to determine the next/previous row with. As such, dynamic cursors are not recommended unless absolutely necessary.
The dynamic cursor supports the following recordset methods when opened with a optimistic lock:
• AddNew
• Delete
• Find
• MoveFirst
• MovePrevious
• Update
• UpdateBatch
While Dynamic cursors can be beneficial for multi-user applications, it is best to avoid them when possible, and work around multi-user issues when possible by calling the resync and requery methods when possible, and executing UPDATE queries that increment and decrement count values instead of using the recordset to do updates (i.e. rather than getting an inventory count in a recordset, incrementing it in VB, and doing a recordset.update, use the connection object to execute a query similar to UPDATE inventory SET count = count - 1 WHERE itemcode = 5)