Jet can be a limiting factor when you’re building applications in a client/server environment. The same features that provide heterogeneous capabilities and comprehensive keyset-driven updatable recordsets will slow client/server performance if these features aren’t needed. (A keyset is simply a set of keys that uniquely identify all records in a resultset.) The DAO and Jet model Recordset objects do, however, have features that can improve performance, although there is a consequent loss of functionality.
One of the keys to understanding how to improve DAO performance is understanding the difference between the way dynaset and snapshot recordsets are handled by Jet. You can improve performance generally by buffering data in local memory (or on disk) so that when the data is requested by an application it’s retrieved from local memory rather than via a network request. Jet automatically buffers data in different ways according to the type of recordset that has been created.
Dynasets are keyset-driven, which means that when a recordset is created, only the primary key for each record is retrieved and cached locally. The result is that dynaset recordsets can be created quickly since the primary key of a row in a recordset is usually much smaller than the size of the entire row. When a row is retrieved with the MoveNext Recordset method, for example, the key that is buffered locally is used to retrieve the rest of the data for the row. This is, of course, the way in which a recordset is dynamic and responds to changes in the underlying data. If another application changes the data in the row the primary key relies upon, the change will be reflected in the data that is retrieved because the data is refreshed each time the row is visited. However, this also means that dynaset-style recordsets produce network traffic every time a row is retrieved.
Snapshots, on the other hand, retrieve the entire row and store it locally. This means that it takes longer to create snapshot-style recordsets than to create the equivalent dynaset-style recordsets. This also explains why snapshots are static with respect to changes made to underlying data rows that have already been retrieved. (Rows that haven’t yet been retrieved might show changes made to the data in these rows, but this is DBMS-dependent.) Once a row is retrieved, however, each subsequent request for data in that row produces no network traffic at all because the request is handled entirely from the local memory buffer. So, it’s a good idea to use snapshots where recordset data is static but used frequently, for example, in reference or lookup tables.