Settings in the Engines subkey of the Windows Registry control how the Microsoft Jet database engine uses memory and performs other aspects of its operations. Microsoft Jet automatically provides default settings that usually give the best performance for most common database operations. However, depending on what kind of operations your application performs and how much memory is available at any given time, you may be able to improve performance by adjusting Windows Registry settings.
Keep in mind that the optimum value for a setting can change from computer to computer, and can change depending on how much memory is available, the kind of operation the user is performing, or the level of activity in the database at any given time. Using a system and working environment that’s as much like your typical user’s system as possible, experiment with these settings to find out what works best.
The default values for these Microsoft Jet settings are written in the \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Jet 3.5 key of the Windows Registry when Microsoft Access is installed. Changes made to settings in this key may affect other applications that use the Jet 3.5 database engine, such as Visual Basic version 5.0 or Microsoft Excel 97. To make changes that only affect Microsoft Access 97, create a Jet 3.5 key below the \HKEY_LOCAL_MACHINE \SOFTWARE\Microsoft\Office\8.0\Access\Jet\3.5\Engines key where you can add values for any default setting you want to override.
If you want changes to Microsoft Jet settings to affect only your application or your session of Microsoft Access, you can create a custom set of Registry keys, called a user profile, that contains settings to override default Registry settings. You use the /profile command-line option when starting Microsoft Access to specify the user profile you want your application to use.
See Also For information on user profiles or changing Microsoft Access Registry settings, search the Help index for “user profiles” or “Registry.”
Tip You can also change most of these settings by using the SetOption method of the DBEngine object in Data Access Objects (DAO) code. Using the SetOption method, you can change these settings “on the fly” while your application is running. For example, you may want to change the SharedAsyncDelay and PageTimeout settings to low values to increase performance when your application performs operations on one record at a time or is using controls bound to data, but increase these values when performing bulk operations such as update and delete queries or transactions on large numbers of records. Changing Microsoft Jet settings by using the SetOption method doesn’t affect the values stored in the Windows Registry. Changes made with the SetOption method are only in effect for the current instance of the DBEngine object. For more information on the SetOption method, search the Help index for “SetOption method.”
The following sections discuss the Windows Registry settings you can adjust to improve performance.
A thread is a software process running independently on a multitasking operating system such as Windows 95 or Windows NT Workstation. For example, the ability to use multiple threads allows you to run a communications software program to download a file at the same time as you use your word processor to edit a document. Microsoft Jet uses threads internally to enhance performance and provide background services such as read-ahead caching, write-behind caching (transaction commit), cache maintenance, and the detection of changes made to shared databases. By default, Microsoft Jet uses up to a maximum of three threads. You may want to try increasing the Threads setting if a large number of actions are performed in your application, or if it contains a large number of linked tables.
Microsoft Jet reads and writes data in 2K pages, placing the data in a temporary holding area called the buffer as required by its operations. By default, when performing operations that add, delete, or update records and that aren’t part of an explicit transaction, Microsoft Jet automatically performs internal transactions that group changes to records and temporarily saves them in the buffer. After a specified time, or when the size specified by the MaxBufferSize setting is exceeded, it then writes the data as a chunk to the database. This minimizes the time spent reading and writing data to the database. Additionally, Microsoft Jet can minimize the time spent reading data for tables, queries, forms, or reports by reading available data from its buffer.
See Also For information on other settings that regulate the management of transactions, see “ImplicitCommitSync,” “ExclusiveAsyncDelay,” and “SharedAsyncDelay” later in this section.
The MaxBufferSize setting specifies a “high water mark” for the size of the buffer that Microsoft Jet uses to work with records in memory, measured in kilobytes. Microsoft Jet can temporarily exceed the MaxBufferSize. As soon as it does, however, it starts a background thread to write data to the database to bring the buffer’s size down to the specified “high water mark.”
By default, Microsoft Jet allocates memory for its internal buffer on an as-needed basis up to the MaxBufferSize. The default value written in the Windows Registry for the MaxBufferSize setting when Microsoft Access is installed is 0, which indicates that Microsoft Jet calculates the setting based on the following formula:
((Total RAM in K - 12,288)/4 + 512K)
For example, on a system with 16 MB of RAM (16,384K), Microsoft Jet uses a MaxBufferSize setting of 1,536.
You can override the default calculated setting by specifying a different MaxBufferSize value. This sets a new “high water mark.” The minimum value that Microsoft Jet uses by default is 512K, but you can specify a value as low as 128K. However, setting the MaxBufferSize to a value less than 512K is not recommended for Microsoft Access applications because it can seriously degrade performance.
For computers with 16 MB or less of installed RAM, there is generally no need to override the calculated setting. For computers with 32 MB of RAM, you may see some performance improvement when specifying a larger MaxBufferSize if no other applications are running at the same time as Microsoft Access. For example, tests have shown performance improvements in applications that perform large transactions when a MaxBufferSize as large as 8 MB is specified.
Specifying a MaxBufferSize larger than 8 MB hasn’t been found to increase performance. Setting a value too high can degrade performance due to the added CPU overhead needed to manage the cache, and due to the fact that the operating system may start swapping Microsoft Jet’s cache to disk as virtual memory.
Important When specifying a MaxBufferSize setting that is larger than the default, you should also increase the SharedAsyncDelay setting to increase the time that data is held in the buffer. If you don’t increase the SharedAsyncDelay setting, Microsoft Jet writes the contents of its buffer to the database before it has had time to utilize the additional memory you specified. For more information on the SharedAsyncDelay setting, see “SharedAsyncDelay” later in this section.
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. In synchronous mode, Microsoft Jet doesn’t return control to the application code until the changes made by the CommitTrans method are written to the database. In asynchronous mode, Microsoft Jet stores the changes in its memory buffer, 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. The default UserCommitSync setting is Yes, which specifies synchronous mode. It is not recommended that you change this setting because in asynchronous mode, there is no guarantee that information has been written to disk before your code proceeds to the next command.
Note If you’ve used explicit transactions to improve performance in previous versions of Microsoft Access, you no longer need to do so. For more information, see “Using Transactions to Update Records” later in this chapter.
See Also For more information on transactions, see “Microsoft Jet Transactions” in Chapter 9, “Working with Records and Fields.”
By default, when performing operations that add, delete, or update records outside of explicit transactions, Microsoft Jet automatically performs internal transactions that temporarily save data in its memory buffer, and then later write the data as a chunk to the disk. The ImplicitCommitSync setting determines whether changes made by using these automatic internal transactions are written to the database in synchronous mode or asynchronous mode. The default setting is No, which specifies that these changes are written to the database in asynchronous mode; this provides the best performance. If you want internal transactions to be written to the database in synchronous mode, change the ImplicitCommitSync setting to Yes. If you change the setting to Yes, you get behavior similar to Microsoft Jet versions 2.x and earlier when you weren’t employing explicit transactions. However, doing so can also impair performance considerably, so it is not recommended that you change this setting.
The FlushTransactionTimeout setting determines Microsoft Jet’s method of performing asynchronous writes to a database file. The FlushTransactionTimeout setting is the number of milliseconds 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, and is the preferred method of determining how Microsoft Jet performs asynchronous writes to a database file. To enable the ExclusiveAsyncDelay and SharedAsyncDelay settings, you must set the FlushTransactionTimeout entry to a value of 0.
The ExclusiveAsyncDelay setting is the maximum time that can pass before asynchronous mode changes start to be written to a database that’s opened 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 MaxBufferSize, you may see some performance improvement if you increase 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 opened 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 buffer waiting to be written to the database. If your application performs operations that affect many records, increase this setting to give Microsoft Jet additional time to temporarily save additions, deletions, and updates in its buffer 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.
The PageTimeout setting determines how long Microsoft Jet waits before checking to see if other users have made changes to the database. If changes have been made, Microsoft Jet refreshes the data in its memory buffer.
Note This action is equivalent to pressing F9 while viewing a table or query in Microsoft Access, or using the Refresh method in DAO code.
The default PageTimeout setting is 5,000 milliseconds. Decreasing this setting increases the amount of time spent reading from the disk, thus impairing performance, but can make the data available to the user more current. The Refresh Interval setting on the Advanced tab of the Options dialog box (Tools menu) in Microsoft Access overrides the PageTimeout setting.
Tip You can override the PageTimeout setting and refresh the cache by using the dbRefreshCache argument of the Idle method in DAO code. This allows users to see other users’ changes immediately. For more information on the Idle method, search the Help index for “Idle method.”
If Microsoft Access tries to place a lock on a page in a shared database and a message that locking has failed is returned, the LockDelay setting determines how long it waits before it retries. If the time it takes to return the message exceeds the LockDelay setting, there is no delay. The default setting is 100 milliseconds. On systems that don’t manage lock retries themselves, such as Windows 95 peer-to-peer networking, the LockDelay setting prevents Microsoft Jet from performing repeated retries over a short period of time. If you are using such a system, the default setting is usually sufficient to reduce the number of lock requests sent across the network, which frees up network bandwidth for other purposes. If your application or its users frequently lock a large number of records, you may want to try increasing this setting to further reduce the number of lock requests being sent across the network. If you are using a server-based networking system that manages lock retries itself, such as Windows NT Server or Novellâ NetWareâ, there is no need to change this setting.
The MaxLocksPerFile setting determines the maximum number of locks that Microsoft Jet places against a file. The default setting is 9,500 locks. If the number of locks required to perform a transaction exceeds the MaxLocksPerFile setting, the transaction commits (writes) the data that has locks associated with it, frees the locks, and then continues processing the transaction. If the maximum number of locks a server can handle is less than the MaxLocksPerFile setting, the server returns an error message or appears to hang when performing a large transaction. If this occurs, you should decrease the MaxLocksPerFile setting. A Novell server can be configured to perform a maximum of 10,000 locks per connection. A Novell server connection can include more than one database, so it’s possible to exceed the maximum number of available locks if you are using more than one database at a time.
Long value (LV) pages store data in fields with Memo, OLE object, and Hyperlink data types, as well as the data that defines forms, reports, and modules. When a database is open in shared mode, the size of the database increases when data, forms, reports, and modules are deleted or changed in such a way that the current LV page must be discarded and replaced with a new LV page. Sometimes a new LV page is needed to prevent errors for other users who may still be using the object or data in its original form.
The RecycleLVs setting determines when discarded LV pages become available for reuse. The default RecycleLVs setting is 0, which specifies that discarded LV pages continue to occupy space in the database and only become available for reuse after the last user closes the database. You can change the RecycleLVs setting to 1 so that discarded LV pages become available for reuse after Microsoft Jet determines that there’s only one user in the database in shared mode and that new LV data has been added to the database. Note that setting RecycleLVs to 1 slows down performance somewhat. Therefore, this is primarily useful when you are creating or modifying forms, reports, and modules because it minimizes the need to compact the database.
When a database is open in exclusive mode, the RecycleLVs setting has no effect: discarded LV pages become available for reuse immediately. In both exclusive mode and shared mode, discarded LV pages are reused only when new LV data needs to be written to the database. To remove discarded LV pages before that time, you must compact the database.