How to optimize Office Access and Jet database engine network performance with Windows 2000-based and Windows XP-based clients

After you upgrade your computer from a Microsoft Windows NT 4.0-based operating system to a Windows 2000-based operating system or to a Windows XP Professional-based operating system, you may experience a decrease in performance of Office Access or of Jet database engine-based applications. For example, you may experience the following symptoms:

• An .mdb file takes longer to open than you expect.
• Access queries take longer to run than you expect.
• Opening a form that is based on a linked table in Access takes longer than you expect.
• Access operations, such as Insert, take longer than you expect.
• The process to access network resources takes longer than you expect.

The network performance of Access and Jet database engine-based programs depends on the following criteria:

• The file system settings of the file server where the Access back-end database is stored.
• The caching and optimization methods of the client.
• The Access or Jet database engine-based program routines and methods.

File server optimization

The following methods describe how to optimize performance on the file server that stores the Access or Jet database engine-based database.

Use 8.3 file name conventions

Access calls the GetShortPathNameW function across the network on each append query if the database file name is longer than eight characters or if the database is located in a folder name that is longer than eight characters.

This behavior occurs with file names and folder names that are longer than the 8.3 file naming convention limits specify. Long file and folder names can increase the time that is required for the query to be completed. If the name of your database file or of the folder where your database is located is longer than eight characters, rename the file name or the folder name. The file and folder names must be no longer than eight characters, and the file name extension must be no longer than three characters. The following is an example of a database path that includes a short, 8.3 convention file and folder names:The following is an example of a database path that uses long file and folder names: For more information about long file names in Windows, click the following article number to view the article in the Microsoft Knowledge Base: Alternatively, if you are using a split database, you can replace the file and folder names of the front-end database links with the 8.3 convention equivalent. For example, assume that you have the following long file and folder name database path: You can rename the links that are in the front-end database to the following short file and folder name equivalent: The following example illustrates how to link to a database that has the long database path with the short file name conversion: For more information about how to replace the file and folder names of the front-end database links with the 8.3 convention equivalent, click the following article number to view the article in the Microsoft Knowledge Base: Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

Turn off the sharing violation notification delay

You can turn off the sharing violation notification delay to improve file server performance. To do this, follow these steps on the file server that stores the Access or Jet database engine-based program database.

Important This section, method, or task contains steps that tell you how to modify the registry. However, serious problems might occur if you modify the registry incorrectly. Therefore, make sure that you follow these steps carefully. For added protection, back up the registry before you modify it. Then, you can restore the registry if a problem occurs. For more information about how to back up and restore the registry, click the following article number to view the article in the Microsoft Knowledge Base:

1. Click Start, click Run, type regedit, and then click OK.
2. Locate the following registry subkey:
   HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanServer\Parameters
3. Right-click Parameters, point to New, click DWORD Value, type SharingViolationDelay, and then press ENTER.
   
   Note When you create the SharingViolationDelay subkey entry, the default value that is assigned is 0x0. This is what we want.
   
   If the SharingViolationDelay subkey entry already exists, right-click the SharingViolationDelay entry, click Modify, type 0, and then click OK.
4. Right-click Parameters, point to New, click DWORD Value, type SharingViolationRetries, and the press ENTER.
   
   Note When you create the SharingViolationRetries subkey entry, the default value that is assigned is 0x0. This is what we want.
   
   If the SharingViolationRetries subkey entry already exists, right-click the SharingViolationRetries entry, click Modify, type 0, and then click OK.
5. Quit Registry Editor.

For more information about the sharing violation notification delay, click the following article number to view the article in the Microsoft Knowledge Base:

Move the back-end database file to an NTFS file system volume

If the Access or Jet database engine-based program database is located on a file allocation table (FAT)-based volume, you can improve performance by moving the back-end database file to an NTFS volume. For more information about NTFS, click the following article number to view the article in the Microsoft Knowledge Base: For more information about how to move Access or Jet database engine database files, search on “copy or move an Access file” in Access Help.

Disable automatic short file name generation

Disable automatic short file name generation on the NTFS file system. To do this, follow these steps on the file server that stores the Access or Jet database engine-based program database. Important This section, method, or task contains steps that tell you how to modify the registry. However, serious problems might occur if you modify the registry incorrectly. Therefore, make sure that you follow these steps carefully. For added protection, back up the registry before you modify it. Then, you can restore the registry if a problem occurs. For more information about how to back up and restore the registry, click the following article number to view the article in the Microsoft Knowledge Base:

1. Click Start, click Run, type regedit, and then click OK..
2. Locate the following registry subkey:
   HKEY_LOCAL_MACHINE \SYSTEM\CurrentControlSet\Control\FileSystem
3. Right-click NtfsDisable8dot3NameCreation, click Modify, type 1, and then click OK.
4. Quit Registry Editor, and then restart the computer.

Note After you make this change, files that use the short name format will still be available to 32-bit programs. However, files with long file names that are created after you make this change may not be available to 16-bit programs.

For more information automatic short file name generation on NTFS, click the following article numbers to view the articles in the Microsoft Knowledge Base: For more information, click the following article number to view the article in the Microsoft Knowledge Base: If your Access or Jet database engine database file is located on a Windows Server 2003 file server, you can turn off file system aliasing. Aliasing is a feature that is included with Windows Server 2003. This feature lets multiple long file names or multiple short file names refer to the same file. Disabling file system aliasing can improve performance by increasing the server service caching that is available on the Windows Server 2003-based computer.

Important If your Windows Server 2003-based computer uses file system aliasing, such as mounting or reparse points, we do not recommend that you follow this procedure.

To turn off file server aliasing, follow these steps on the Windows Server 2003 file server that stores the Access or Jet database engine database.

Important This section, method, or task contains steps that tell you how to modify the registry. However, serious problems might occur if you modify the registry incorrectly. Therefore, make sure that you follow these steps carefully. For added protection, back up the registry before you modify it. Then, you can restore the registry if a problem occurs. For more information about how to back up and restore the registry, click the following article number to view the article in the Microsoft Knowledge Base:

1. Click Start, click Run, type regedit, and then click OK.
2. Locate the following registry subkey:
   HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanServer\Parameters
3. Right-click Parameters, point to New, click DWORD Value, type NoAliasingOnFileSystem, and then press ENTER.
4. Right-click NoAliasingOnFileSystem, click Modify, type 1, and then click OK.
5. Quit Registry Editor, and then restart the computer.

Client optimization

The following methods describe how to optimize performance on the client that will access the Access or Jet database engine-based database.

Enable advanced file name caching

By default, Windows 2000-based and Windows XP-based operating systems only cache short file names and short folder names. That is, file names and folder names that comply with the 8.3 convention. You can enable advanced caching on your Windows 2000-based or Windows XP-based computer so that it will also cache long file names and long folder names. This can improve performance when you access files over a network.

For more information about how to enable advanced caching with Windows XP Service Pack 1 and earlier versions, click the following article number to view the article in the Microsoft Knowledge Base: For more information about how to enable advanced caching on Windows 2000, click the following article number to view the article in the Microsoft Knowledge Base: Note Windows XP Service Pack 2 and Windows Server 2003 already include a type of advanced caching. However, you must set the InfoCacheLevel registry entry in the following registry subkey to a hexadecimal value of 0x10 for optimized performance:

Optimize append queries on Windows XP-based computers

Windows XP-based computers flush the cache and write the whole database to the file server for each append transaction that occurs. You can optimize append queries by applying a hotfix and changing the Windows registry entry DisableFlushOnCleanup on your Windows XP-based computer. For more information about how to optimize append queries on Windows XP-based computers, click the following article number to view the article in the Microsoft Knowledge Base:

Optimize the Access or Jet database engine-based database routines and methods

The following recommendations can improve the performance of the routines and methods that are used by Access or Jet database engine-based programs.

• Use a split database configuration. A split database configuration is also known as a front-end and back-end database configuration. For more information about how to configure a split database, click the following article number to view the article in the Microsoft Knowledge Base:
  162522

  (http://support.microsoft.com/kb/162522/ )

  Issues when you redistribute an Access application as a split database application
• Install the latest service pack that is available for your Windows operating system on the client computers and on the file server computer.
• Install Jet 4.0 Service Pack 8 or a later version on the client computer. For more information about how to obtain the latest service pack for the Jet database engine, click the following article number to view the article in the Microsoft Knowledge Base:
  239114

  (http://support.microsoft.com/kb/239114/ )

  How to obtain the latest service pack for the Microsoft Jet 4.0 database engine
• Configure all database front-end computers to maintain an open connection to the back-end database files. To do this, create a table in the back-end database file that contains one text field and one record. For example, create a table that has the following configuration:
  Table Name: tblConnect
  Field Name: Field1
  Data type: Text
  First record: “Connection”
  
  Link this table to your front-end database and create a form that is based on the table. Open the database by using a hidden form with your startup routine. Your startup routine can be put in a macro or in a startup form, as in the following example:

  DoCmd.OpenForm " tblConnect",acNormal ,,,,acHidden

  Alternatively, you can open a recordset that is based on this table. The recordset variable has to be declared in a global declaration section of a module. It also has to be closed when you exit the front-end database.
• Use forms instead of tables when multiple users connect to the database for data entry.

Best practices for optimizing database performance

We recommend the following best practices for optimizing Access or Jet database engine-based program performance:

• Optimize the database program code in Access modules that include resource intensive operations, such as loops. To do this, enclose the loop code with BeginTrans and CommitTrans statements, such as in the following example:

  BeginTrans
  		  Loop code
  CommitTrans

  This enables the Jet database engine to accumulate multiple updates and write them as a single batch. For more information about how to optimize code in Access modules, click the following article number to view the article in the Microsoft Knowledge Base:
  208858

  (http://support.microsoft.com/kb/208858/ )

  ACC2000: Optimizing for client/server performance
• By default, opportunistic locking is enabled on Windows clients. Make sure that opportunistic locking has not been disabled on the client computer. For more information about how to configure opportunistic locking in Windows, click the following article number to view the article in the Microsoft Knowledge Base:
  296264

  (http://support.microsoft.com/kb/296264/ )

  Configuring opportunistic locking in Windows
  For more information about opportunistic locking and performance, click the following article number to view the article in the Microsoft Knowledge Base:
  303528

  (http://support.microsoft.com/kb/303528/ )

  How to keep a Jet 4.0 database in top working condition
• Connect to the file server that stores the Access or Jet database engine-based database by using a mapped drive instead of a UNC path.

To optimize database performance, consider migrating your Access or Jet database engine-based programs to Microsoft SQL Server. The client/server model is more appropriate than the file server model for active databases that service many connections. Using SQL Server can dramatically improve performance and increase robustness.

For more information about Access, Jet database engine and network performance, click the following article numbers to view the articles in the Microsoft Knowledge Base: