Channels ▼
RSS

Pooling Database Connections


Opening a connection to a database engine (even Microsoft SQL Server) is an expensive proposition (in terms of time). Whether you use ODBC or OLEDB to interact with the database engine, the negotiation between client and server to establish initial communication over a transport (likely TCP/IP), authenticate the user (even more time consuming if using Windows Authentication due to the need to communicate with the domain controller on the network), and verify access to a specified database. You wouldn’t want to do this more often than you had to. However, in some disconnected environments like web applications, it can be impossible to maintain a live connection to the database over a long period of time. The challenge is then how to reuse a connection previously established without having to keep the connection “variable” around in your code.

Before you head off to devise such a mechanism, it turns out the ODBC and OLEDB both provide a means to reuse connections-ODBC refers to this as "connection pooling while OLEDB refers to this as "resource pooling. Though the terms differ, the underlying concept is the same. Rather than have application code manage open connections to a database, the operating system manages this pooling. This allows connections to be reused across a set of applications and is thus more efficient. From here forward, I'll just refer to the concept as "pooling.

Pooling works by servicing requests for connections to a database by checking an available pool of similar connections. If none are available that match the connection parameters of the request, a new connection is made to the database (which is just as expensive as if you made it yourself). However, once that connection is "closed by the application, it is simply added to the pool. It is not really closed in the sense that the underlying communication channel with the database server is terminated. Instead, the connection is kept open and is made available to the next request that comes in. The key part of this is that the connection request must exactly match the request that originally generated the pooled connection. If it doesn't and there are no other exact matches, then the driver will generate another connection that does match.

This has important ramifications if you decide to use per-user rather than per-application (or proxy-user) connections. A per-user connection is, by definition, a unique connection and can't be shared by other users. Especially in a multiuser server application that services hundreds or thousands of requests, it is unlikely that the database engine will be able to efficiently handle such a large number of open connections. In this case, you should consider dedicated a given user account (a proxy user) that is used to establish the connection-this allows the entire user base to pool connections to the database since the connection requests would be the same. In general, per-user connection strings are only useful in limited situations where you have a small, defined set of users and want to manage database access by user account. One compromise: If you find you still need some type of user rights grouping at the database level, you must create a set of database users with varying rights and use one of these users as a proxy for an incoming request. You'd have to manage this relationship externally and determine which database user should be associated with a given request-using Windows Authentication and Windows User Groups to do this wouldn't work because, although SQL Server can grant rights to specific User Groups, the authentication would still be done on a per-user basis, which would mean no connection pooling (essentially).

If you are using ODBC at the SDK level to interact with a database, turning on connection pooling requires a little bit of code. However, once activated, the underlying ODBC driver will handle the rest. Here is how that code would look:

SQLHENV henv;
		SQLAllocEnv(&henv);

		//enable connection pooling
		SQLRETURN ret =
			SQLSetEnvAttr(
		         NULL,
				 SQL_ATTR_CONNECTION_POOLING,
		         (SQLPOINTER)SQL_CP_ONE_PER_DRIVER,
				 SQL_IS_INTEGER);
		if ( ret != SQL_SUCCESS )
			return false;

		SQLHDBC hdbc;
		ret = SQLAllocConnect(henv,&hdbc);
		if ( ret != SQL_SUCCESS )
			return false;

You would typically do this during the initialization step of your application. When the app terminates, you would cleanup with the following:

if ( hdbc )
		SQLFreeConnect(hdbc);
	if ( henv )
		SQLFreeEnv(henv);
	hdbc = NULL;
	henv = NULL;

	//disable connection pooling
	SQLRETURN ret = SQLSetEnvAttr(
         NULL,
         SQL_ATTR_CONNECTION_POOLING,
         (SQLPOINTER)SQL_CP_OFF,
         SQL_IS_INTEGER); 
	if ( ret != SQL_SUCCESS )
		return false;

Pretty straightforward. However, it gets even easier if you are using the OLEDB for ODBC or OLEDB for SQL Server drivers. In these cases, you can activate connection pooling by turning on OLE DB Services via the connection string as in the following example (assuming you are using ADO):

LPCSTR conn = "Provider=SQLOLEDB.1;
               Data Source=MyServer; 
               Catalog=MyDb;
               User ID=MyName; 
               Password=MyPassword; 
               OLE DB Services=-1;

The last value in the connection string (OLE DB Services) tells the driver to turn on full support for OLEDB and, thus, connection pooling. That's all you need to do.

If you are working at the OLEDB SDK (or COM) level using ATL, you have to write some more code similar to what you do for ODBC, except that this code is written for each connection rather thans once up front. Here is a snippet of how that would look:

		CDataSource db;
		CDBPropSet	dbinit(DBPROPSET_DBINIT);


		dbinit.AddProperty(DBPROP_AUTH_USERID, "MyName);
		dbinit.AddProperty(DBPROP_INIT_DATASOURCE, "MyServer);
		dbinit.AddProperty(DBPROP_INIT_CATALOG, "MyDb );
		dbinit.AddProperty(DBPROP_INIT_PROMPT, (short)4);
		dbinit.AddProperty(DBPROP_INIT_LCID, (long)1033);
		dbinit.AddProperty(DBPROP_INIT_OLEDBSERVICES,
				(long)DBPROPVAL_OS_ENABLEALL);
		HRESULT hr = db.OpenWithServiceComponents(_T("sqloledb"),
 				   &dbinit);

Note the code to initialize OLEDB Services similar to what we did for ADO.

Using connection (or resource) pooling within an application is straightforward once you know what knobs to turn to activate it. Just remember the restrictions on how pooling works with per-user connections. If you aren't currently using connection pooling in your application, you might try activating it with one of these approaches and see what kinds of performance gains you see. Write to me and let me know what the results are.


Mark M. Baker is the Chief of Research & Development at BNA Software located in Washington, D.C.
Do you have a Windows development question? Send it to markbaker-winqa8364@mailblock.com.


Related Reading


More Insights






Currently we allow the following HTML tags in comments:

Single tags

These tags can be used alone and don't need an ending tag.

<br> Defines a single line break

<hr> Defines a horizontal line

Matching tags

These require an ending tag - e.g. <i>italic text</i>

<a> Defines an anchor

<b> Defines bold text

<big> Defines big text

<blockquote> Defines a long quotation

<caption> Defines a table caption

<cite> Defines a citation

<code> Defines computer code text

<em> Defines emphasized text

<fieldset> Defines a border around elements in a form

<h1> This is heading 1

<h2> This is heading 2

<h3> This is heading 3

<h4> This is heading 4

<h5> This is heading 5

<h6> This is heading 6

<i> Defines italic text

<p> Defines a paragraph

<pre> Defines preformatted text

<q> Defines a short quotation

<samp> Defines sample computer code text

<small> Defines small text

<span> Defines a section in a document

<s> Defines strikethrough text

<strike> Defines strikethrough text

<strong> Defines strong text

<sub> Defines subscripted text

<sup> Defines superscripted text

<u> Defines underlined text

Dr. Dobb's encourages readers to engage in spirited, healthy debate, including taking us to task. However, Dr. Dobb's moderates all comments posted to our site, and reserves the right to modify or remove any content that it determines to be derogatory, offensive, inflammatory, vulgar, irrelevant/off-topic, racist or obvious marketing or spam. Dr. Dobb's further reserves the right to disable the profile of any commenter participating in said activities.

 
Disqus Tips To upload an avatar photo, first complete your Disqus profile. | View the list of supported HTML tags you can use to style comments. | Please read our commenting policy.
 

Video