Connection Pooling in Asp.Net

Connection Pooling in Asp.Net

Connecting to a database server typically consists of several time-consuming steps.

A physical channel such as a socket or a named pipe must be established, the initial handshake with the server must occur, the connection string information must be parsed, the connection must be authenticated by the server, checks must be run for enlisting in the current transaction, and so on.

In practice, most applications use only one or a few different configurations for connections. This means that during application execution, many identical connections will be repeatedly opened and closed. To minimize the cost of opening connections, ADO.NET uses an optimization technique called connection pooling.

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. 

Whenever a user calls Open on a connection, the pooler looks for an available connection in the pool. If a pooled connection is available, it returns it to the caller instead of opening a new connection. 

When the application calls Close on the connection, the pooler returns it to the pooled set of active connections instead of closing it.

Once the connection is returned to the pool, it is ready to be reused on the next Open call.

Only connections with the same configuration can be pooled. ADO.NET keeps several pools at the same time, one for each configuration. 

Connections are separated into pools by connection string, and by Windows identity when integrated security is used. Connections are also pooled based on whether they are enlisted in a transaction. When usingChangePassword, the SqlCredential instance affects the connection pool. 

Different instances of SqlCredential will use different connection pools, even if the user ID and password are the same.

Pooling connections can significantly enhance the performance and scalability of your application. By default, connection pooling is enabled in ADO.NET. Unless you explicitly disable it, the pooler optimizes the connections as they are opened and closed in your application. 

You can also supply several connection string modifiers to control connection pooling behavior.

In Simple Words:

We said that by default we have 100 pools. When the first user enters our webpage and tries to open one connection, then the first connection pool is created. 

Now 99 pools are remaining. At the same time, one more user tries to open a connection with the same string then our pooler checks whether any opened pools with same connection string are free or available.

If the first user closes the connection, that pool will be free and this user starts using the same pool. So this second user also uses the same pool and we can get more performance and resources. 

Suppose if the pool is not freed and the first user is still using it, what the pooler will do from the remaining 99 pools is that it will create one more pool and use it. 

Like this, when n number of users try to establish a connection if the pools are free it will reuse the same pool or if 100 pools are engaged, it will wait for a pool to be free. 

If the pool is getting free in a particular time, it will use it otherwise the waiting connection will expire. So for huge customer websites, 100 pools will not be enough. 

So we have to allow more pools.

By Default we have minimum 0 and maximum 100 pools.