C# .NET - Pooling parameter in Connection String

Asked By Naresh Kumar on 12-Jan-10 01:17 AM
Hi All,

Can any one please explain about Pooling, what happens when I add pooling= true in connection string.

Thanks in advance.

re - Santhosh N replied to Naresh Kumar on 12-Jan-10 01:22 AM

To Say straight, it increases the application performance and this is because Connection pooling reduces the number of times that new connections must be opened. The pooler maintains ownership of the physical connection. It manages connections by keeping alive a set of active connections for each given connection configuration..

check here for more info

Pooling in ADO.net - Kalit Sikka replied to Naresh Kumar on 12-Jan-10 01:23 AM

Pooling connections can significantly enhance the performance and scalability of your application. The .NET Framework Data Provider for SQL Server provides connection pooling automatically for your ADO.NET client application. You can also supply several connection string modifiers to control connection pooling behavior (see the section "Controlling Connection Pooling with Connection String Keywords" later in this topic).

Pool Creation and Assignment

When a connection is opened, a connection pool is created based on an exact matching algorithm that associates the pool with the connection string in the connection. Each connection pool is associated with a distinct connection string. When a new connection is opened, if the connection string is not an exact match to an existing pool, a new pool is created.

In the following example, three new SqlConnection objects are created, but only two connection pools are required to manage them. Note that the first and second connection strings differ by the value assigned for Initial Catalog.

SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Integrated Security=SSPI;Initial Catalog=northwind";
conn.Open();      
// Pool A is created.
 
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Integrated Security=SSPI;Initial Catalog=pubs";
conn.Open();      
// Pool B is created because the connection strings differ.
 
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Integrated Security=SSPI;Initial Catalog=northwind";
conn.Open();      
// The connection string matches pool A.

 

Pooling - Kalit Sikka replied to Naresh Kumar on 12-Jan-10 01:32 AM

The process of establishing a database connection can be time consuming depending upon network connectivity. Connection pooling is a viable option if the network is an issue, and database server resources are available. 

There are several steps involved with establishing a database connection. First, you will establish a connection to the database server over the network. Next, the connection string is parsed and the user authenticated. Finally, the connection is established, and operations may be performed. Connection pooling allows an application to maintain ownership of a database connection.

Connection pooling maintains a group (or pool) of active database connections. When an application tries to open a database connection, an open connection is retrieved from the pool (if available). Closing a connection returns it to the pool for other processes to utilise.

ADO.NET pools connections with the same connection or configuration (connection string). It can maintain more than one pool (actually, one for each configuration). An interesting note: Connection pooling is utilised (by default) unless otherwise specified. If you close and dispose of all connections, then there will be no pool (since there are no available connections).

While leaving database connections continuously open can be troublesome, it can be advantageous for applications that are in constant communication with a database by negating the need to re-open connections. Some database administrators may frown on the practice since multiple connections (not all of which may be useful) to the database are open. Using connection pooling depends upon available server resources and application requirements (i.e., does it really need it).

Using connection pooling
Connection pooling is enabled by default. You may override the default behaviour with the pooling setting in the connection string. The following SQL Server connection string does not utilise connection pooling:

Data Source=TestServer;Initial Catalog=Northwind;
User ID=
Chester;Password=Tester;Pooling=False;

You can use the same approach with other .NET Data Providers. You may enable it by setting it to True (or eliminating the Pooling variable to use the default behavior). In addition, the default size of the connection pool is 100, but you may override this as well with connection string variables. You may use the following variables to control the minimum and maximum size of the pool as well as transaction support:

·                          Max Pool Size: The maximum number of connections allowed in the pool. The default value is 100.

·                          Min Pool Size: The minimum number of connections allowed in the pool. The default value is zero.

·                          Enlist: Signals whether the pooler automatically enlists the connection in the creation thread's current transaction context. The default value is true.

The following SQL Server connection string uses connection pooling with a minimum size of five and a maximum size of 100:

Data Source=TestServer;Initial Catalog=Northwind;
User ID=
Chester;Password=Tester;Max Pool Size=50;
Min Pool Size=5;Pooling=True;

You should refer to the documentation if you're using a .NET Data Provider other than SQL Server. Other data providers may have more pooling options. A good example is the Oracle Data Provider, which offers two options -- Decr Pool Size and Incr Pool Size -- for controlling how a connection pool may shrink or grow.

The Microsoft documentation states that the connection pooler satisfies requests for connections by reallocating connections as they are released back into the pool. If the maximum pool size has been reached and no usable connection is available, the request is queued. The pooler then tries to reclaim any connections until the time-out is reached (the default is 15 seconds). If the pooler cannot satisfy the request before the connection times out, an exception is thrown.

Pooling advice
You should be judicious in your use of connection pooling. Here are a few tips when using it:

·                          Only open connections when needed. That is, timing is everything, so open a connection just before you need it and not any sooner. Also, close that connection as soon as you are finished with itâ€"don't wait for the garbage collector to do it.

·                          Close user-defined transactions before closing related connections.

·                          To maintain the connection pool, you should keep at least one connection open. Therefore, do not close all your connections in the pool. If server resources become a problem, you may close all connections, and the pool will be recreated with the next request.

·                          Do not use connection pooling if integrated security is utilised. This results in a unique connection string per user, so each user has a connection pool that is not available to other users. The end result is poor performance, so pooling should be avoided in this scenario.

ADO.NET 2.0
Two new connection pooling-related methods have been introduced with ADO.NET 2.0. They are ClearAllPools and ClearPool. ClearAllPools clears the connection pools for a given provider, and ClearPool clears the connection pool that is associated with a specific connection. If there are connections in use at the time of the call, they are marked appropriately. When they are closed, they are discarded instead of being returned to the pool.

Another application option
It may seem odd that a column focusing on cleaning up database resources is followed by another one outlining connection pooling. The key is to know what is available and to use it appropriately depending on application requirements. If an application is in constant communication with a database, then connection pooling may be optimal since the need to open/establish connections is negated, thus performance improves. On the other hand, an application that runs nightly does not need to maintain a pool since it does nothing with the database the rest of the day. Use your best judgment when deciding whether to use connection pooling.

PLEASE REFER: http://www.builderau.com.au/program/dotnet/soa/Take-advantage-of-Net-connection-pooling/0,339028399,339269541,00.htm

REPLY - paresh tank replied to Kalit Sikka on 12-Jan-10 01:47 AM

     CONECTION POOLING BASIC ..

Opening a database connection is a resource intensive and time consuming operation. Connection pooling increases the performance of Web applications by reusing active database connections instead of creating a new connection with every request. Connection pool manager maintains a pool of open database connections. When a new connection requests come in, the pool manager checks if the pool contains any unused connections and returns one if available. If all connections currently in the pool are busy and the maximum pool size has not been reached, the new connection is created and added to the pool. When the pool reaches its maximum size all new connection requests are being queued up until a connection in the pool becomes available or the connection attempt times out.

Connection pooling behavior is controlled by the connection string parameters. The following are four parameters that control most of the connection pooling behavior:

  • Connect Timeout - controls the wait period in seconds when a new connection is requested, if this timeout expires, an exception will be thrown. Default is 15 seconds.
  • Max Pool Size - specifies the maximum size of your connection pool. Default is 100. Most Web sites do not use more than 40 connections under the heaviest load but it depends on how long your database operations take to complete.
  • Min Pool Size - initial number of connections that will be added to the pool upon its creation. Default is zero; however, you may chose to set this to a small number such as 5 if your application needs consistent response times even after it was idle for hours. In this case the first user requests won't have to wait for those database connections to establish.
  • Pooling - controls if your connection pooling on or off. Default as you may've guessed is true. Read on to see when you may use Pooling=false setting.

OK...........

Pooling parameter in Connection String - Naresh Kumar replied to Kalit Sikka on 12-Jan-10 01:55 AM
Thanks.
Re - Huggy Bear replied to Naresh Kumar on 12-Jan-10 02:10 AM
In a datadriven .net application there is always a requirement to access the database almost for all user actions on the application. Ideally what people do to increase the performance of the application is they create a single connection and maintain it throughout without closing it, so that every time the same connection object is been used. Consider the case of web applications, where the objects are stateless and if you let the connections unclosed, you can't get them to use again because of the statelessness behaviour. So ultimately what will happen is the orphaned connection count will grow up which is not a desired one.

This is where connection pooling comes in handy, you can specify a max pool size also. When you enable connection pooling in your application, when a connection is requested for the first time a pool is created in the database by ADO.NET and a connection is returned from that pool. Once the database operation is completed if you Close and dispose the connection object, it doesn't actually gets disposed instead it is returned back to the pool so any other subsequest database request can make use of that connection object. Here by it provides a good mechanism for maintaining the database connection objects and boosts the application performance considerably.
Pooling - Naresh Kumar replied to Huggy Bear on 12-Jan-10 02:32 AM
Thanks for the reply, I have some doubts please clarify.


I have a made a request for the first time with the connection string A, pool A is created. Suppose this is in busy state serving  in the same time another request with the same connection string A is made. What happens in this scenario a new connection will be opened in Pool A or another pool is created.


Thanks in advance.


Re - Huggy Bear replied to Naresh Kumar on 12-Jan-10 02:48 AM
It will use only the existing pool and if there are any free connection objects available in the pool apart from the one being used, it will make use of the free one, else it will wait for the connection to be freed up by the other function.