Connection pooling
An application connect to database with the help of connection. Creating a connection is high resource consuming process .To establish a connection , a connection string is required , which is either from machine.config or web.config or from hard coded string . To create a connection , a physical socket channel or named pipe is established, handshaking between client and server occurred.
Ado.Net architecture is disconnected in nature, web application frequently fires sql query to the database for data manipulation. Each time a query is fire, physical connection is required to execute this query. To establish connection each time, performance of the web application degraded and make quality of the application poor.
To speed up the process , connection pool is used. Connection pool is a technique to reuse existing connection. The pool maintain the ownership of the physical connection. When a connection is free, it is keep in a connection pool and make the connection alive. When the next connection is ask, connection pool first check, if there is any available connection in the pool . If there is available connection in the pool, connection is assign from the pool else a new connection is established.
Connection pooling reduce the number of times new connection open, increase performance and scalability of the application.
Connection pool remove when ISS reset or application domain unloaded.
Another option is ClearAllPools and ClearPool to clear the connection for a given provider and for a specific connection respectively.
Here are some points related to the connection pooling
* If you want to pull a set of connection, the connection string must be same.
* Security context of each connection must be same.
* All connection must be comes under the same process.
Below is the example of connection pool
Example
using
System;
using
System.Collections.Generic;
using
System.Linq;
using
System.Web;
using
System.Web.UI;
using
System.Web.UI.WebControls;
using
System.Data;
using
System.Data.SqlClient;
public
partial
class
Default2
: System.Web.UI.Page
{
protected
void
Page_Load(object
sender, EventArgs
e)
{
string
sqlConnectString = "Data
Source= xxx.xxx.xxx.xxx; initial catalog=MyDatabaseName;Connect
Timeout=6000; User ID=MyUserID;Password=MyPassword";
SqlConnection
connection = new
SqlConnection();
connection.ConnectionString
= sqlConnectString + "Connection
Timeout=30;Connection Lifetime=0;Min Pool Size=0;Max Pool
Size=100;Pooling=true;";
connection.Open();
//for
command executing
SqlCommand
cmd = new
SqlCommand("select
* from tbl_myTable");
cmd.Connection
= connection;
SqlDataReader
dr = cmd.ExecuteReader();
//for
data set
SqlDataAdapter
da = new
SqlDataAdapter("select
* from tbl_myTable",
connection);
}
}
You can control the connection pool by the help of this property
MaxPoolSize : Maximum number of connection is allowed in the pool.Default value is 100
MinPoolSize : Minimum number of full allowed in the connection pool. Default value is 0.
Pooling: Boolean value true or false. when true connection is drawn from pooling.
Connection Timeout: Maximum number of second to wait for a connection to be established. The default value is 15.
Incr Pool Size : Number of connection to be established when all connection are in use. The default value is 5.
How a connection pool can be visible. Connection pool can be visible in the SQL server.
Exec sp_who / sp_who2 stored procedure return result that number of connection is there in the database . You can also monitor connection pooling performance from SQL profiler also.
Example 1
SELECT DB_NAME(dbid)
as
Data_Base_Name,
COUNT(dbid)
as
Number_Of_Connections,
loginame
as
Login_uSER_Name
FROM sys.sysprocesses
WHERE
dbid
GROUP
BY
dbid,
loginame
Output
Result
----------------------------------------
database1 3 user1
database2 4 user1
----------------------------------------
database1 3 user1
database2 4 user1
