"Steven Blair" <st**********@btinternet.com> wrote in message
news:O3****************@TK2MSFTNGP05.phx.gbl...
| As I understand it, if I create a connection object in my application
| and close the connection, the next time I open a connection with the
| same connection string I should be using a pooled connection?
|
| Is this possible over different instances of a class.
|
| For example
|
| Instance 1 of my dll is alive and creates a connection to a Database.
| The class goes out of scope (I have closed the connection by this stage)
| and instance 2 is now alive and I create a conneciton using the same Db
| string.
|
| Can I use a pooled connection in this scenario.
|
| I ask this ebcause its taking me around 140ms to create a connection to
| a sql2005 each time I receive a message from another application.
| However, we have some visual basic code which is doing the same thing
| which takes 200ms for first connection, but sebsequent connections <1ms
| (A new instance of the VB dll is created for each message).
|
| Steven
|
|
|
| *** Sent via Developersdex
http://www.developersdex.com ***
Connection pooling is a complex beast, let me try to explain how Connection
pooling works.
Note that each provider may use different protocols and heuristics, here I'm
only talking about the SQL client provider.
Connection pools are per 'application domain' containers who are maintained
on a per db 'instance' per security context basis. That means that multiple
pools can exist in a process/application domain at the same time depending
on the instance and security context. Note also that two different AD's
cannot share the same pool.
A pool is established the first time you create a connection with a certain
instance using specific credentials. This is the most costly operation (say
150 msec's.) as it involves :
- the creation of the connection pool,
- an authentication handshake (a network logon) and,
- the establishment of a physical network connection.
When your application opens a subsequent connection with the same DB
instance using the same credentials, the provider will search the pool for a
free connection entry and return this one to your application. This is a
very cheap operation (a few hundred µsec's. or less) as it doesn't involve
any security handshake nor physical connection.
If no free entry exists in the pool, a new connection will be created with
the server, no authentication has to be done as long as a security context
exists between the client and the server, such contexts is maintained,by the
provider, per existing pool.
Now, when your application closes a connection, it's returned to the pool
where it waits (a reconnect time-out period) for a new open request from
your application to the same db instance using the same security context. If
no connection arrives before the 'reconnect time-out' expires, the
connection with the DB is closed and the entry removed from the pool. If
this entry was the last entry in the pool, the next connection request will
be more costly as the provider needs to establish a physical connection with
the db instance (can take >20 msec's. over TCP/IP), but it won't be as
costly as the first connection at pool creation time.
Now back to your findings, you seems to have measured the first connection
time, but you didn't tell us what kind of application you are talking about,
also you keep refereeing to DLL's and classes without specifying the
context. How are these assemblies loaded, are they AD loaded and unloaded,
what's the time interval between subsequent open calls?
Why don't you measure the effect of connection pooling by running a simple
console application?
Willy.