Introduction
One of the most
common sources of trouble for the
beginning VB / MySQL developer seems
to revolve around which cursor
location to use. Related to the
cursor location problem is the
choice of cursor type and lock type.
The purpose of this article is to
assist the Visual Basic developer in
choosing the proper cursor location,
cursor type, and lock type to use
when programming Visual Basic (or
VB) applications that use MySQL as a
back-end database through ADO and
Connector/ODBC (MyODBC).
This article will
assume that the reader is using the
MySQL RDBMS, but should apply to
developers using other database
management systems. For an overview
of why MySQL is a good choice for
Visual Basic developers, see the
Why VB/MySQL article on this
site. This sample requires the
latest version of Connector/ODBC (MyODBC),
available for download
here (currently 3.51.06).
On the Visual Basic
side, I recommend you have service
pack 5 installed for VB, which you
can download
here. Additionally, the latest
version of MDAC (Microsoft Data
Access Components) should be
installed, and can be found
here (currently 2.7 SP1).
Finally, this article applies to ADO
2.7 and VB6. It is not applicable to
ADO.NET under VB.NET (Unless you
reference ADODB in your project and
use it for database access).
What Are Cursors?
In ADO, when we talk
about cursors, we are essentially
talking about a set of rows. When
you execute a query that returns
rows of data, such as
SELECT *
FROM mytable
, the
resulting data is handled using a
cursor. A cursor can be located
either on the client with the
adUseClient
argument,
or on the server with the
adUseServer
argument.
In addition, the are 4 types of
cursor:
adOpenForwardOnly
,
adOpenStatic
,
adOpenDynamic
, and
adOpenKeyset
.
The different types
and locations will be discussed in
further detail below. Your choice of
cursor type and cursor location will
affect what you are able to do with
the data you retrieve, and how
changes made to the data by other
users are reflected in your copy of
the data.
Cursor Location
The ADODB.Connection
object (the ADO object used to
broker all data exchanges between
the VB application and the MySQL
server) has a property known as
CursorLocation
which
is used to set/retrieve the cursor
location that will be used by any
recordset objects that access their
data through the connection object.
The
CursorLocation
property can only be set while the
connection is closed, and the
property will be inherited by any
recordset objects that access their
data through the given connection
object. Recordset objects can also
explicitly set a
cursorlocation
different than the connection
objects
cursorlocation as long as it
is set before the recordset is open.
The two options available for this
property are
adUseClient
and
adUseServer
, with
adUseServer
being the
default property.
adUseServer
When using the
adUseServer
server-side
cursorlocation,
responsibility for handling the data
generated by a query lies with the
database server. MySQL itself does
not support server-side cursors, so
the data handling is actually done
by the Connector / ODBC driver. The
benefit of server-side cursors is
that we gain access to the dynamic
cursor type. This allows us to see
any changes to the data that are
made by other users in the data our
application is accessing.
For example: let's
say we are selling tickets to a
concert with our application, we
need to know that a given seat is
available for sale in real-time to
ensure we do not double-book the
seat. With a server-side cursor, we
can be sure that the data we are
manipulating is the most current
possible. In addition, we have the
ability to lock the data we are
working on as we edit it, to make
sure our changes are going to be
posted to the database successfully.
With a server-side
cursor (adUseServer),
we have access to the
adOpenDynamic
and
adOpenForwardOnly
cursor types, and all four of the
recordset lock types, which will be
discussed below.
It should be noted
that using a server-side cursor, and
the
adOpenDynamic cursor in
particular, will result in a
significant performance loss, and
should be avoided if at all
possible. In addition, certain
functionality, such as the
RecordCount
property of a Recordset and the
GetChunk and Appendchunk function
for handling BLOB data, will fail or
return abnormal results when used
with a server-side cursor.
adUseClient
Client-side cursors,
specified with the
adUseClient
keyword, are handled internally by
ADO. These cursors offer more
functionality than their server-side
counterparts, and also result in
less load being placed on the
server. Most advanced ADO
functionality is designed for use
with client-side cursors, and I
personally use client-side cursors
for all my applications (with one
exception).
When using a
client-side
adUseClient
cursor, only the
adOpenStatic
cursor is available, and we cannot
use the
adLockPessimistic
lock
type (see below).
Client-side cursors
also help decrease load on our MySQL
server, since with a static cursor
data is sent to the client and then
the server has no further
communications with the client. This
allows your server to scale a lot
better than with server-side
cursors.
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
)
Lock Types
While cursor
locations and cursor types specify
how our data is going to be handled,
the lock type property specifies how
we are going to lock the underlying
data to protect any changes we make
and ensure they are processed. There
are four different lock types, and
the locktype is set in the recordset
object as part of the open method
(it can also be set using the
LockType property of the recordset
object). The four locktypes are:
adLockReadOnly
(default),
adLockOptimistic
,
adLockPessimistic
, and
adLockBatchOptimistic
.
All four locktypes are available to
a server-side cursor, the
adLockPessimistic
locktype is unavailable to a
client-side cursor.
adLockReadOnly
The default lock type
is
adLockReadOnly.
A
read-only lock is the most efficient
when accessing data, as there is no
checking for data changes and
therefore no extra traffic between
the client and server while loading
records.
As the name implies,
using a read-only lock will block
you from making any changes to the
table. If you find yourself with an
error message like "Current
recordset does not support
updating", then you need to change
away from the default
adLockReadOnly
lock
type.
adLockOptimistic
An optimistic lock is
used for modifications that either
happen in a low-concurrency
environment, or where having
multiple users making changes to the
same records is not a major concern.
With an optimistic lock, the table
or row locks will occur when the
update method of the recordset
object is called. This will ensure
the change is successfully made, but
will not prevent other users from
changing the underlying data while
you are modifying it in VB.
The
adLockOptimistic
lock
type is typically your best choice
when deciding on a table lock for a
non-read-only situation. In almost
all my applications, the only two
lock types I use are
adLockReadOnly
and
adLockOptimistic
.
adLockBatchOptimistic
When using the
adBatchOptimistic lock type,
your changes will be cached locally
until the recordset's
UpdateBatch
method is called. When
UpdateBatch
is called, all changes will be
pushed to the server in a group.
This can make the bulk insert of a
large number of records more
efficient. (Note: Calling
ALTER
TABLE mytable DISABLE KEYS
before a large batch of inserts,
followed by
ALTER TABLE mytable ENABLE
KEYS
after the batch
completes, can dramatically speed up
the batch insert process, as MySQL
can rebuild an index faster than it
can add one entry at a time).
adLockPessimistic
In a situation of
high concurrency, with multiple
users modifying the same data, you
may need a pessimistic lock type.
With
adLockPessimistic
, the
underlying rows (or table) will be
locked as soon as you begin making
changes to the current record, and
will not be unlocked until the
Update method is called.
While this will
ensure that you do not have
overlapping changed with other
users, it could cause performance
issues, especially with a
MyISAM
table, with features table-level
locking only. Make sure that the
changes are immediately followed by
the recordset's update method, and
that there is no break for user
input between a change and the
update in order to ensure no long
breaks (and potentially canceled
locks by the database) in the
operation of the database.
While
adLockPessimistic
has
it's place, the same advice I gave
regarding dynamic cursors applies:
avoid it when possible, as it is
very resource intensive and involves
a lot more work both on the client
and server side.
Conclusion
While there are a
large number of potential
CursorType/CursorLocation
combinations, the ones that are
currently available to the MySQL/VB
developer are:
adUseClient/adOpenStatic,
adUseServer/adOpenForwardOnly,
and
adUseServer/adOpenDynamic.
For most uses,
adUseClient/adOpenStatic
is your best choice, with
adLockReadOnly
as your lock type for any read-only
operations (export to a file, load
rows to a listview, combobox, etc.)
and
adLockOptimistic as your lock
type for any read/write operations.
adOpenDynamic
and
adLockPessimistic are best
suited for high-concurrency
situations where you need to ensure
that multiple users do not corrupt
each other's data. While these offer
the most current views of data and
the most restrictive locking, they
do so at a severe price as far as
performance is concerned.
The combination of
adUseServer/adOpenForwardOnly/adLockReadonly
offers the best performance overall
for operations like populating
controls and exporting to files.
When combined with option 1048576
(Don't cache query results),
adOpenForwardOnly also
provides excellent memory
efficiency, as only one record at a
time is loaded into memory. Be awate
that if a locktype other than
adLockReadOnly
is used, memory usage will slowly
build as rows are loaded into memory
and kept in case they are needed for
an update or cancel statement. The
one row at a time operation is only
present with an
adLockReadOnly/adOpenForwardOnly
combination.