Microsoft Jet 3.5 can write changes to the database in either synchronous mode or asynchronous mode.
In synchronous mode, Microsoft Jet doesn’t return control to the application code until the changes are written to the database. If transactions are used, the changes are written when the CommitTrans method is used; if transactions are not used, the changes are written every time the Update method is used. Synchronous mode is the only mode available in Microsoft Jet 2.x.
In asynchronous mode, Microsoft Jet stores the changes in its cache, returns control to the application code immediately, and then writes the changes to the database in a background thread. Microsoft Jet begins writing the changes either after a specified period of time (determined by the FlushTransactionTimeout setting, or by the SharedAsyncDelay or ExclusiveAsyncDelay settings described later in this section) or when the MaxBufferSize is exceeded.
Asynchronous (or background) writes can improve performance in several ways:
The following sections describe the registry settings that are used to control how Microsoft Jet performs disk writes.
The UserCommitSync setting determines whether changes made as part of an explicit transaction (a change made to data by using the BeginTrans, CommitTrans, and Rollback methods) are written to the database in synchronous mode or asynchronous mode. The default value for the UserCommitSync setting is Yes, which specifies synchronous mode. It is not recommended that you change this value because in asynchronous mode, there is no guarantee that information has been written to disk before your code proceeds to the next command.
By default, when performing operations that add, delete, or update records outside of explicit transactions, Microsoft Jet automatically performs internal transactions called implicit transactions that temporarily save data in its memory cache, and then later write the data as a chunk to the disk. The ImplicitCommitSync setting determines whether changes made by using implicit transactions are written to the database in synchronous mode or asynchronous mode. The default value of the ImplicitCommitSync setting is No, which specifies that these changes are written to the database in asynchronous mode; this provides the best performance. If you want implicit transactions to be written to the database in synchronous mode, change the value of the ImplicitCommitSync setting to Yes. If you change the value of the setting to Yes, you get behavior similar to Microsoft Jet versions 2.x and earlier when you weren’t using explicit transactions. However, doing so can also impair performance considerably, so it is not recommended that you change the value of this setting.
Note There is no longer a need to use explicit transactions to improve the performance of Microsoft Jet. A database application using Microsoft Jet 3.5 should use explicit transactions only in situations where there may be a need to roll back changes. Microsoft Jet can now automatically perform implicit transactions to improve performance whenever it adds, deletes, or changes records. However, implicit transactions for SQL DML statements were removed in Microsoft Jet 3.5. For more information, see “Removal of Implicit Transactions for SQL DML Statements” later in this chapter.
See Also For more information on transactions, see “Microsoft Jet Transactions” in Chapter 5, “Working with Records and Fields.”
The FlushTransactionTimeout setting determines how Microsoft Jet performs asynchronous writes to a database file. The FlushTransactionTimeout setting is the number of milliseconds of inactivity after which Microsoft Jet starts writing database changes to disk from its cache. Changes are written to disk after the specified amount of time has expired and if no new pages have been added to the cache during that interval. The only exception is if the size of the cache exceeds the MaxBufferSize setting, at which point the cache starts asynchronous writes regardless of whether the time has expired. The default setting is 500 milliseconds. The only reason to increase the value of the FlushTransactionTimeout setting is if the database is being updated over a slow wide area network (WAN) or local area network (LAN) connection. Increasing this value for databases being updated over fast WAN and typical LAN connections does not improve performance.
The FlushTransactionTimeout setting overrides both the ExclusiveAsyncDelay and SharedAsyncDelay registry settings that are used to control asynchronous writes in Microsoft Jet 3.0. The FlushTransactionTimeout setting is the preferred method of determining how Microsoft Jet 3.5 performs asynchronous writes to a database file. Although it is not recommended that you do so, you can enable the ExclusiveAsyncDelay and SharedAsyncDelay settings by setting the value of the FlushTransactionTimeout setting to 0.
The ExclusiveAsyncDelay setting is the maximum time that can pass before asynchronous mode changes start to be written to a database that’s open exclusively. The default setting is 2,000 milliseconds. Decrease this setting if you want to be sure that changes are written to your database more frequently, but note that this decreases overall performance. Because the default setting is already quite long, increasing this setting doesn’t improve performance in most cases. However, if your system has 32 MB of RAM or more and you have specified a large value for the MaxBufferSize setting, you may see some performance improvement if you increase the value of this setting. By default, this setting is disabled by the FlushTransactionTimeout setting.
The SharedAsyncDelay setting is the maximum time that can pass before asynchronous mode changes start to be written to a database that’s open in shared mode. The default setting is 50 milliseconds. Note that this produces a small delay before changes are made available to other users. Increasing this value enhances performance in shared databases because there are fewer disk writes. However, it may reduce the overall concurrency because pages are locked while they are in the Microsoft Jet cache waiting to be written to the database. If your application performs operations that affect many records, increase the value of this setting to give Microsoft Jet additional time to temporarily save additions, deletions, and updates in its cache before it writes them to the database. This applies whether your application uses action queries, DAO code, or SQL statements to add, delete, or update records. By default, this setting is disabled by the FlushTransactionTimeout setting.