INFO: ODBC Connection Pooling and ADO

• Microsoft Active Server Pages, versions 1.0, 1.0b
• ActiveX Data Objects (ADO)
• Microsoft Visual InterDev, version 1.0

SUMMARY

This article answers the following connection pooling questions:

• What is connection pooling?
• When would I use connection pooling?
• What are the advantages of using connection pooling?
• How do I enable connection pooling in ActiveX Data Objects (ADO)?

MORE INFORMATION

What is Connection Pooling?

The following excerpt is from the Odbc.hlp file in the Microsoft ODBC 3.0 SDK:

   Connection pooling enables an application to use a connection from a
   pool of connections that do not need to be reestablished for each use.
   Once a connection has been created and placed in a pool, an application
   can reuse that connection without performing the complete connection
   process.

What Are The Advantages Of Using Connection Pooling?

Here is an excerpt is from the Odbc.hlp file in the Microsoft ODBC 3.0 SDK:

"Using a pooled connection can result in significant performance gains, because applications can save the overhead involved in making a connection. This can be particularly significant for middle-tier applications that connect over a network, or for applications that repeatedly connect and disconnect, such as Internet applications.

"In addition to performance gains, the connection pooling architecture enables an environment and its associated connections to be used by multiple components in a single process. This means that standalone components in the same process can interact with each other without being aware of each other. A connection in a connection pool can be used repeatedly by multiple applications."

When Would I Use Connection Pooling in ADO?

Connection pooling is recommended when using ODBC drivers that support multiple threads. For example, the Microsoft ODBC Driver for Oracle, and the Microsoft SQL Server ODBC driver.

The Microsoft Access ODBC driver (Jet driver) is one driver that requires that connection pooling be turned off. The Jet driver does not support multiple threads using it concurrently. Because a connection may end up being dropped by a different thread than the thread that created it, it is recommended that connection pooling be turned off for this driver.

How do I Enable/Disable Connection Pooling in ADO?

To enable/disable connection pooling in ActiveX Data Objects (ADO), you need to change a registry entry on your Internet Information Server that has Active Server Pages installed on it.

CAUTION: Using the Registry Editor incorrectly can cause serious problems, including corruption that may make it necessary to reinstall Windows or Active Server Pages. Using the Registry Editor to edit entries in the registry is equivalent to editing raw sectors on a hard disk. If you make mistakes, your computer’s configuration could be damaged. You should edit registry entries only for settings that you cannot adjust through the user interface, and be very careful whenever you edit the registry directly.

To make the change, follow these steps:

1. Use the Start menu to run Regedit.exe.

2. Navigate to the following key:

   HKEY_LOCAL_MACHINE

        \SYSTEM
          \CurrentControlSet
            \Services
              \W3SVC
                \ASP
                  \Parameters
   
   

3. Double-click the StartConnectionPool item.

4. Change the "Value Data" item to 1 to enable connection pooling or 0 to disable connection pooling.

5. Click OK.

6. Close REGEDIT.

More details on connection pooling can be found in the documentation provided with Active Server Pages (ASP). After installing ASP, you will find a group in your Windows Start menu named "Microsoft Internet Server." This group has a link to the ASP documentation called "Active Server Pages Roadmap." Open this link, and follow the following links:

• In the left frame, in the tree view, click on the plus next to "Component Reference."
• Open the "Database Access Component" by clicking on the plus sign next to it.

REFERENCES

For the latest Knowledge Base articles and other support information on Visual InterDev and Active Server Pages, see the following page on the Microsoft Technical Support site:

   http://support.microsoft.com/support/vinterdev/

Keywords          : AXSFDataBase AXSFSQL kbprg
Version           : 1.0 1.0b
Platform          : NT WINDOWS
Issue type        : kbinfo