Setting up the TCP connection between your Web application and SQL Server can
be an expensive operation. Connection pooling allows to reuse connections to
the database. Rather than setting up a new TCP connection on each request, a
new connection is set up only when one is not available in the connection
pool. When the connection is closed, it is returned to the pool where it
remains connected to the database, as opposed to completely tearing down that
TCP connection.
Of course you need to watch out for leaking connections. Always close your
connections when you're finished with them. Always call Close or Dispose
explicitly on your connection when you are finished with it. The CLR will
eventually destroy the class and force the connection closed, but you have no
guarantee when the garbage collection on the object will actually happen.
To use connection pooling optimally, there are a couple of rules to live by.
First, open the connection, do the work, and then close the connection. It's
okay to open and close the connection multiple times on each request if you
have to... rather than keeping the connection open and passing it around
through different methods. Second, use the same connection string (and the
same thread identity if you're using integrated authentication) . If you don't
use the same connection string, for example customizing the connection string
based on the logged-in user, you won't get the same optimization value
provided by connection pooling. And if you use integrated authentication
while impersonating a large set of users, your pooling will also be much less
effective. The .NET CLR data performance counters can be very useful when
attempting to track down any performance issues that are related to
connection pooling.
--
Misbah Arefin
https://mcp.support.microsoft.com/profile/MISBAH.AREFIN http://www.linkedin.com/in/misbaharefin
"Tony Johansson" wrote:
Hello!
How does this connection pool function actually ?
As I have been told is that when the connection is returned to the pool
the connection to the database is still open. What you do is that you just
return a still open connection to the pool.
When I open a connection I get an already open connection to the database.
Is this correct understood ?
//Tony
