Sunday, October 12, 2008

How do I enable or disable connection pooling?

Connection pooling is enabled by default for SQL Server and Oracle, so you shouldn't need to configure anything to take advantage of this feature. (Connection pooling allows you to reuse existing connections from a "pool" instead of instantiating and creating a new connection object from scratch.)

The number of seconds before an idle connection in the pool is discarded is stored in the following registry key:

\HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\\CPTimeout

You will notice that this key is absent for file-based database platforms, such as Access, dBase and Excel. If you are using Access through the Jet/OLEDB driver, you can add the following to the connection string to force connection pooling:

OLE DB Services=-1

Please test the performance of your app with and without this setting.

Microsoft states:

"To make the best use of connection pooling, explicitly close database connections as soon as possible. By default, a connection terminates after your script finishes execution. However, by explicitly closing a connection in your script after it is no longer needed, you reduce demand on the database server and make the connection available to other users."

No comments: