Microsoft Jet reads and writes data in 2K pages, placing the data in a temporary holding area called the cache as required by its operations. By default, when performing operations that add, delete, or update records and aren’t part of an explicit transaction, Microsoft Jet automatically performs internal transactions that group changes to records and temporarily saves them in the cache. After a specified amount of time, or when the size specified by the MaxBufferSize setting is exceeded, it then writes the data as a chunk to the database. This is analogous to the way an operating system’s lazy-write cache works. 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 cache.
See Also For information on other settings that control how transactions are written to disk, see “Asynchronous Writes” later in this section.
The MaxBufferSize setting specifies a “high water mark” for the size of the cache that Microsoft Jet uses to work with records in memory, measured in kilobytes. Microsoft Jet can temporarily exceed the MaxBufferSize setting. As soon as it does, however, it starts a background thread to write data to the database to bring the cache’s size down to the specified “high water mark.”
By default, Microsoft Jet allocates memory for its cache on an as-needed basis up to the size specified by the MaxBufferSize setting. 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. Because computers with 64 MB or more of RAM are becoming more common, Microsoft Jet 3.5 sets a maximum of 13,824K on the result of the formula to prevent Microsoft Jet from claiming an overly large amount of RAM for the cache.
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 setting 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 or more, 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 the Microsoft Jet cache to disk as virtual memory.
Important If you have specified a MaxBufferSize setting that is larger than the default and have disabled the FlushTransactionTimeout setting, you should also increase the SharedAsyncDelay setting to increase the time that data is held in the Microsoft Jet cache. If you don’t increase the SharedAsyncDelay setting, Microsoft Jet writes the contents of its cache to the database before it has had time to utilize the additional memory you specified. For more information on the SharedAsyncDelay setting, see the following section “Asynchronous Writes.”