PRB: REPLACE Command Runs Slowly When Applied to a Remote Table (259928)

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

SYMPTOMS

If a REPLACE ALL statement (or a REPLACE statement that affects a very large number of records in a table) is applied to a relatively large table that resides on a remote computer and the network is Microsoft Windows NT-based and uses the Transmission Control Protocol/Internet Protocol (TCP/IP) protocol, the statement may take a very long time to complete.

RESOLUTION

Do not permit the redirector to use the cache manager to cache the contents of files. This is accomplished by disabling the UtilizeNtCaching startup parameter for the Workstation Service.

Warning If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.

You can modify the startup parameters for the Workstation Service by using the Registry Editor. Unless otherwise indicated, these value entries are located in the following Registry path:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanWorkstation\Parameters

Move to this Registry path and add the following value if it is not already there:

Value Name:	UtilizeNtCaching
Data Type:	REG_DWORD
Data:	0
Radix:	Hex

After making this change, you must restart the computer.

Note Disabling Windows NT caching may result in a performance decrease with some applications.

MORE INFORMATION

The REPLACE statement appears to execute at an acceptable rate for about the first 25 percent of the records that are replaced. After that, however, no replacements occur for a minute or more. From this point on, replacements occur in spurts between long time delays until all of the replacements have completed.

The UtilizeNtCaching parameter is enabled by default. This parameter indicates whether the redirector uses the cache manager to cache the contents of files. Disable this parameter only to guarantee that all data is flushed to the server immediately after it is written by the application.

Steps to reproduce the behavior

Create a table that is at least 350 MB in size.
From your local PC, execute a REPLACE ALL statement from the Visual FoxPro Command window. Make this statement replace one field with one value on the table that you created in step 1.

Note You may be able to achieve further performance improvements by using the SYS(3050) function. See the "References" section for articles providing more information on this function.

REFERENCES

For additional information about Windows NT registry entries, click the following article numbers to view the articles in the Microsoft Knowledge Base:

102981 Workstation service entries

163401 How to disable network redirector file caching

For additional information about the SYS(3050) function, click the following article numbers to view the articles in the Microsoft Knowledge Base:

176483 Large amounts of RAM seem to process data slowly

190312 Computer "not responding" when certain commands issued

129311 Visual FoxPro has 3 new SYS functions related to memory