BUG: Slow Performance Opening MDAC 2.5 ODBC DSN on Win2000 (245488)

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

When running ODBC applications on Microsoft Windows 2000, you might notice a decrease in performance accessing ODBC System Datasources (DSNs). This performance decrease can be up to ten percent.

CAUSE

When connecting to an ODBC datasource name (DSN), ODBC searches the DSNs in the HKEY_CURRENT_USER\Software\ODBC key first. If the DSN is not found, HKEY_LOCAL_MACHINE\Software\ODBC is searched. There is a significant amount of overhead with each registry function, and there is the possibility of locking within the registry. This causes the drop in performance.

NOTE: Registry key locking is even more probable in a high thread count situation.

RESOLUTION

Testing has demonstrated that DSN-less connections are the highest performance connection option. If a DSN-less connection is not appropriate for a particular user scenario, it has been shown that user DSNs outperform system DSNs. Finally, if system DSNs are used, and the scenario does not require the use of user DSNs, you can improve performance by deleting the user DSN from within the ODBC Administrator.

Deleting all unused user DSNs improves performance because ODBC has fewer user DSNs to look through and consequently it looks in the HKEY_LOCAL_MACHINE\Software\ODBC key sooner.

STATUS

Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.