In brief, connection pooling allows applications to reuse an existing database connection from a pool of already established connections, instead of having to repeatedly re-establish new connections to that same database. Establishing database connections is fairly expensive if you compare that aspect to the overall work that will be handled with a database connection in the first place. It is expensive because when a user establishes a connection to a database, the user must be identified and pass authentication (security) before the connection is even allowed. Repeating this over and over puts a huge burden on the database management system and the server, not to mention the overall network traffic that results. This, in turn , directly affects the overall performance of your system and can limit its scalability significantly.
A simple breakdown of the time spent with connection establishment versus actual database work being done with a short query to the Customers table of the Northwind database supplied with Microsoft SQL Server 2000 yielded 84% of the time in the connection/authentication part, and 16% in the actual retrieval and display of a single row from this table. Wow, that's expensive when compared to the overall transaction. It is also ripe for some type of improvement; hence, connection pooling.
So, once again, the overall design approach is to avoid repeatedly establishing these database connections if you can!
Connection pooling is handled at the .NET data provider level. In other words, each .NET data provider, such as the SQL Server .NET data provider and the OLE DB .NET data provider, has a connection-pooling capability embedded in it. Figure 17.1 depicts this aspect.
There are slight differences in how connection pooling works with the different .NET data providers. But, in general, this is fairly transparent from the .NET programmer's point of view.
What will actually happen is that when a connection is opened to a database, a connection pool entry, based on the connection string, will be automatically created in the connection pool (on a per-process basis). These pools are not destroyed until the process ends.
A connection pool entry would be created when the following connection string is used in a SqlConnection open :
All subsequent connections are pooled (utilized) through an exact match algorithm on the connection string. If even one character is different in another connection string that is connecting to the same server and database (that is conceptually the same connection string), a new connection pool will be created and any others that might have been present will not be used. The following connection string, although almost exactly the same as the earlier one, will be considered different because it is not an exact match.
In addition, several connection-string keywords in the connection string change or adjust the behavior of the connection pool. These are as follows :
Connection Lifetime ” When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds this value. A value of zero (0) will cause pooled connections to have the maximum timeout. Zero is also the default. The only time you would vary this value is when you are in a load-balancing situation where you want connection pools to expire fairly quickly so that work can be spread out to other servers.
Connection Reset ” Determines whether the database connection is reset when being removed from the pool. In all cases, the connection state (such as the database context) is not being reset. The default value is "true".
Enlist ” When this is set to "true", the pooler automatically enlists the connection in the current transaction context of the creation thread (if a transaction context exists). If no transaction context exists, it is ignored. The default value is "true".
Max Pool Size ” Indicates the maximum number of connections allowed in this specific connection pool. The correct setting depends on your needs here. If the application has an opportunity to take advantage of the connection pool in a big way, a large value will be specified. Otherwise, smaller values will suffice. The default value is 100.
Min Pool Size ” Indicates the minimum number of connections that will be present ( maintained ) in this specific connection pool. So, if you know that the application will be making frequent usage of the connection pool, set this up to be enough to make a difference quickly. What will actually happen is when the first open connection occurs, more connection pool entries will be created up to the Min Pool Size you specified. The default value is 0.
Pooling ” Indicates that a connection should be drawn from the appropriate connection pool, or if necessary, created and added to the appropriate connection pool. The default value here is "true". In other words, you are, by default, using connection pooling.
The following Visual Basic code is a typical example of defining a connection string with a few of the connection-string keywords that control connection-pooling behavior. In addition, this code opens the connection and, transparently to us, an entry will be created in the connection pool based on this connection string.
Dim connString1 as String connString1 = "server=localhost;Trusted_Connection=yes;database=northwind;" & _ "connection reset=true;" & _ "connection lifetime=0;" & _ "enlist=true;" & _ "min pool size=1;" & _ "max pool size=50" Dim myConnection1 as SqlConnection = new SqlConnection(connString1) Dim CustomerAdapter1 As SqlDataAdapter = New SqlDataAdapter ("SELECT CustomerID, CompanyName, ContactName " & "FROM Customers WHERE CustomerID like 'B%' " & "ORDER BY CustomerID", myconnection1) myConnection1.Open()
Perhaps the most important thing to remember is to close the connection. This releases the connection back to the pool for possible reuse. If you don't close the connection explicitly, it will not be released back to the pool (thus defeating the whole purpose of establishing connection pools to begin with).
It is also interesting to see that the connection pool is actually divided up into multiple transaction-specific pools and one pool for connections not currently enlisted in a transaction. This makes it easier to work with enlisted transaction connections.