Poor performance of Jet 4.0 databases under Windows 7/ Windows Server 2008 R2

TSB1032 | Thursday, 29 March 2012

finPOWER, finPOWER Connect, emPOWER and fdPOWER can use either a Microsoft Access or Microsoft SQL Server database. This article applies to Jet 4.0 (Microsoft Access 2000 format) databases running on Windows 7 or Windows Server 2008 R2 (either 32bit or 64bit) operating systems.

finPOWER, finPOWER Connect, emPOWER and fdPOWER only support Jet 3.5 and 4.0 (Microsoft Access 97 and 2000 formats). It will not open later versions of Access databases, ie the ACE format introduced in Access 2007.

Overview

Running a query in Access on a Windows 7 or Windows Server 2008 R2 computer may take longer than expected to return the query results.

Note, this issue does not occur if you run the query on a computer that is running Windows XP.

Microsoft have acknowledged the issue with Jet 4.0, but it appears that it may not be fixed as this product is currently deprecated. This issue will therefore continue to affect any new Windows 7 computers in the future.

Note, a similar issue exists with ACE databases, ie databases created in Access 2007 and 2010, and Microsoft has released a hotfix to resolve this, see http://support.microsoft.com/kb/2553116 for more information. However, finPOWER, finPOWER Connect, emPOWER and fdPOWER do not use Access 2007 or higher database formats.

Issue

The issue appears to be due to the Windows "leaking memory". This is caused by the system slowly making small incremental increases in system memory usage while a query is running - and this makes the query run slower.

Solution

A Jet 4.0 setting "MaxBufferSize" can be increased to workaround this issue.

The default setting of zero allows the buffer to increase in value as required, but it can cause queries to perform very slowly while this is occurring. Also, it appears the first call to any database can determine the buffer setting to be used for the session and even subsequent connections to other databases. So it's possible that the setting can be set appropriately initially but then be insufficient for subsequent queries, impairing performance unexpectedly.

You may increase the "MaxBufferSize" setting for Jet 4.0 by editing the Windows Registry.

The correct value to increase this setting to varies dependent on various factors, including the overall size of the database, the specific query etc. For a small database "50,000" should be sufficient, a large database may need "200,000" or more. It is recommended that the value entered is not greater than is required, as this may also affect performance; so it is better to try a smaller value and test and increase the value further if required.

The actual operating system being used will determine which of the following registry keys needs to be adjusted.

Windows 7 - 32Bit
Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 4.0\MaxBufferSize

Windows 7 - 64Bit
Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Jet 4.0\MaxBufferSize

WARNING: Serious problems might occur if you modify the Windows Registry incorrectly. See http://support.microsoft.com/kb/136393 before modifying your Windows Registry.