Microsoft KB Archive/889588

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

Article ID: 889588

Article Last Modified on 10/30/2006

-

APPLIES TO


 * Microsoft Windows XP Professional
 * Microsoft Windows XP Professional for Itanium-based systems
 * Microsoft Windows XP Media Center Edition 2002
 * Microsoft Windows XP Tablet PC Edition
 * Microsoft Windows 2000 Professional Edition
 * Microsoft Windows 2000 Server
 * Microsoft Windows Server 2003, Standard Edition (32-bit x86)
 * Microsoft Office Access 2003
 * Microsoft Access 2002 Standard Edition
 * Microsoft Access 2000 Standard Edition
 * Microsoft Access 97 Standard Edition
 * Microsoft Windows XP Professional x64 Edition

-



Important This article contains information about modifying the registry. Before you modify the registry, make sure to back it up and make sure that you understand how to restore the registry if a problem occurs. For information about how to back up, restore, and edit the registry, click the following article number to view the article in the Microsoft Knowledge Base:

256986 Description of the Microsoft Windows Registry



SUMMARY
''When you run a Microsoft Jet database engine-based program, such as Microsoft Office Access, on your Microsoft Windows 2000-based or Microsoft Windows XP-based computer, the program may appear slower and less responsive than you expect. This article contains information about how you can optimize network performance for Windows 2000-based and Windows XP-based computers. Doing this can make Office Access and Jet database engine-based programs more responsive.''



INTRODUCTION
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.



MORE INFORMATION
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:

\Folder_1\Folder_2\AccessDb.mdb

The following is an example of a database path that uses long file and folder names:

\FolderForFirstDatabase\FolderForSecondDatabase\ThisIsA_BigDatabase.mdb

For more information about long file names in Windows, click the following article number to view the article in the Microsoft Knowledge Base:

226403 Short (8.3) file names may change when copied

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:

\FolderForFirstDatabase\FolderForSecondDatabase\ThisIsA_BigDatabase.mdb

You can rename the links that are in the front-end database to the following short file and folder name equivalent:

\Folder~1\Folder~2\Thisis~1.mdb

The following example illustrates how to link to a database that has the long database path with the short file name conversion: Function mcrLink

DoCmd.TransferDatabase acLink, &quot;Microsoft Access&quot;, &quot;\\ServerName\sharename\Folder~1\Folder~2\Thisis~1.mdb&quot;, acTable, &quot;tblName1&quot;, &quot;tblName1&quot;, False

End Function 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:

891176 Slower performance in Access-based or Jet database-based programs after you upgrade from Windows NT 4.0 to Windows 2000 or to Windows XP

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.

Warning Serious problems might occur if you modify the registry incorrectly by using Registry Editor or by using another method. These problems might require that you reinstall your operating system. Microsoft cannot guarantee that these problems can be solved. Modify the registry at your own risk.  Click Start, click Run, type regedit, and then click OK. Locate the following registry subkey:

 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. 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. Quit Registry Editor.</ol>

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

150384 Shared file access is delayed if the file is open on another computer

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:

100108 Overview of FAT, HPFS, and NTFS file systems

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. Warning Serious problems might occur if you modify the registry incorrectly by using Registry Editor or by using another method. These problems might require that you reinstall your operating system. Microsoft cannot guarantee that these problems can be solved. Modify the registry at your own risk. <ol> Click Start, click Run, type regedit, and then click OK..</li> Locate the following registry subkey:

</li> Right-click NtfsDisable8dot3NameCreation, click Modify, type 1, and then click OK.</li> Quit Registry Editor, and then restart the computer.</li></ol>

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:

121007 How to disable the 8.3 name creation on NTFS partitions

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

210638 How to disable automatic short file name generation

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.

Warning Serious problems might occur if you modify the registry incorrectly by using Registry Editor or by using another method. These problems might require that you reinstall your operating system. Microsoft cannot guarantee that these problems can be solved. Modify the registry at your own risk. <ol> Click Start, click Run, type regedit, and then click OK.</li> Locate the following registry subkey:

</li> Right-click Parameters, point to New, click DWORD Value, type NoAliasingOnFileSystem, and then press ENTER.</li> Right-click NoAliasingOnFileSystem, click Modify, type 1, and then click OK.</li> Quit Registry Editor, and then restart the computer.</li></ol>

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:

834350 Your access to network resources is slower in Windows XP than in earlier versions of Windows

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:

843418 You may experience decreased performance when you access network resources or when you use Microsoft Access in Windows 2000

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:

825433 Poor performance when you append data to a shared file-based database from a Windows XP-based client

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. <ul> 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 Issues when you redistribute an Access application as a split database application

</li> Install the latest service pack that is available for your Windows operating system on the client computers and on the file server computer.</li> 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 How to obtain the latest service pack for the Microsoft Jet 4.0 database engine

</li> 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:

<pre class="fixed_text">DoCmd.OpenForm &quot; tblConnect&quot;,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.</li> Use forms instead of tables when multiple users connect to the database for data entry.</li></ul>

Best practices for optimizing database performance
We recommend the following best practices for optimizing Access or Jet database engine-based program performance: <ul> 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:

<pre class="fixed_text">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 ACC2000: Optimizing for client/server performance

</li> 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 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 How to keep a Jet 4.0 database in top working condition

</li> <li>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.</li></ul>

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.

<div class="references_section">