The Microsoft Jet 3.0 database engine used by Microsoft Visual Basic
version 4.0 has many functional improvements over previous versions. This
article briefly describes some of these improvements and summarizes their
effect in contrast with previous versions.
Improvements in Microsoft Jet 3.0 database engine:
New index structure. This results in substantial reduction in storage
size and significant reduction in time to create indices that are highly
No more read locks on index pages. This will remove many locking
conflict issues and remove the need for users to call FreeLocks in
New mechanism for page reuse. In Microsoft Jet 2.x, if a user deleted a
lot of data, the last user out would pay a substantial time penalty
while the recycled page identifiers were being processed into available
page identifiers. In Microsoft Jet 3.0, this process is significantly
New compacting method. Compacting the database now results in the
indices being stored in a clustered-index format. While the clustered
index isn't maintained until the next compact, performance is still
improved. This differs from Microsoft Jet 2.x where rows of data were
stored the way they were entered. The new clustered-key compact method
is based on the primary key of the table. New data entered will be in
New mechanism for page allocation. In Microsoft Jet 2.X each page could
contain data from a different table, thus causing more disk activity. In
Microsoft Jet 3.0, each table typically maintains clusters of grouped
together pages (eight pages), thus eliminating the scenario of every
other page having data from different tables. This scenario also greatly
enhances Microsoft Jet's read-ahead capabilities because data pages are
Faster delete operations. In Microsoft Jet 2.x, pages were doubly
linked, thus causing a delete operation to visit every page when all
rows were being deleted. This operation can now be done by simply
visiting the Space Map object for that table and removing the bits that
reference that table and returning them to the recycle area.
Multithreading. By default, Microsoft Jet uses three threads to perform
read ahead, write behind, and cache maintenance.
Implicit transactions. Users are no longer forced to explicitly use
BEGINTRANS and COMMITTRANS to gain performance improvements. For
example, when walking through a recordset to do operations, Microsoft
Jet groups a series of commands into an internal transaction. Then as
appropriate (that is, in a specified time period or when the cache fills
up) it starts asynchronously flushing the transaction to disk. Still, if
a user wants to force a particular behavior in their application, then
they should explicitly use transactions. For users that don't explicitly
use transactions and have the database opened shared, they should
encounter little if any behavioral differences from Microsoft Jet 2.x.
If a user opens the database exclusively, then they could see some
behavior differences relating to some types of error trapping if they
are not using explicit transactions.
New sort engine. Many of the sort algorithms that are in FoxPro are now
used in Microsoft Jet.
Long Values (such as memos or binary data types) are stored in separate
tables in Microsoft Jet 3.0. In Microsoft Jet 2.x, all long value
columns were stored in one hidden table, which resulted in a lot of
contention issues for users using Microsoft Jet in shared mode. Placing
these values in separate tables resolved manu contention issues. Also
each Long Value table is now indexed, whereas in Microsoft Jet 2.x, Long
Value data was forward linked only, thus making random positioning
Dynamic buffering. Microsoft Jet 2.x supported a maximum cache size of
4 MB. Microsoft Jet 3.0's cache is dynamically allocated at start up
and has no limit. Currently, the cache will be half the available memory
upon start up. This allows effective memory usage on computers that have
large amounts of RAM without changing registry settings. The buffer has
also been redesigned to use common buffering code. In Microsoft Jet 2.x,
separate buffering code could result in pages being needlessly flushed
from the cache, thus causing Jet to re-read the page. Microsoft Jet 2.x
also used a FIFO (first in, first out) buffer replacement policy, which
also caused frequently-referenced pages to be flushed from cache.
Microsoft Jet 3.0 implements an LRU (least recently used) replacement
policy to help eliminate this problem.