Connection pooling

[Steven Blair]

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

 

[Kevin Spencer]

Pooled Connections are managed by the Framework, not by individual
assemblies. So, what assembly opens a Connection is irrelevant. They all
work with the same pool.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Neither a follower nor a lender be

 

[Steven Blair]

I am not so sure about that.

I have been doing some tests today.

If I run my app once which opens a connection to a Sql2005 Db it takes
rouhgly 120ms. If I rerun the app, it takes 120ms again. Therefore I
would say its safe to say connection pooling isnt being used.
If I create a dll that opens a connection and call it from a console app
(create 2 instances of the dll in the console app) my first conenction
is 120ms and my 2nd is <1ms.

It seems the pooling works on an application level.

What do you think?

 

[Willy Denoyette [MVP]]

| 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
|
|
|
|

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.

 

[Steven Blair]

Thanks for the indepth reply

I trieds using a simple app. After the first connection (rouhgly about
140ms) subsequent conenctions are <1ms.

My main application however involves a new dll being created each time
my application receives a message:

Message 1 arrives, new dll is created, and therefore a connection is
created. I dont some processing and the conenction is then closed.
2nd message is received and a new instance of the dll is created and
same again, a new connection is created.

It does appear that this 2nd connection uses a pooled connection since
the time is <1ms.

The conenction strings do not change, so this is why I assumed the
pooling works on an application basis, ie, my exe is the driver program
which kicks off x amount of dll instances.

 

[Steven Blair]

Here is breif outline of the full app:

A multi threaded TCP service (writting in C++) receives a number of
messages. Each message spawns a new instance of a C# dll via COM.
I have only tested the dll's being created via a Console app, but I
suspect (and a little hope) that it works the same. My first instance of
the C# dll will create the pool for this app, and subsequent dll's can
use that pool.

 

[Willy Denoyette [MVP]]

| Thanks for the indepth reply
|
| I trieds using a simple app. After the first connection (rouhgly about
| 140ms) subsequent conenctions are <1ms.
|
| My main application however involves a new dll being created each time
| my application receives a message:
|
| Message 1 arrives, new dll is created, and therefore a connection is
| created. I dont some processing and the conenction is then closed.
| 2nd message is received and a new instance of the dll is created and
| same again, a new connection is created.
|
You don't create instances of DLL's, what exactly do you mean by this?

| It does appear that this 2nd connection uses a pooled connection since
| the time is <1ms.
|

This proves that connection pooling is used, right?

| The conenction strings do not change, so this is why I assumed the
| pooling works on an application basis, ie, my exe is the driver program
| which kicks off x amount of dll instances.
|

You don't kick off dll instances, so you must mean something else.

Willy.

 

[Steven Blair]

When a thread gets created on my TCp service, I create a new instance of
a c# dll, thats what I meant when I said "kick off".

I create an instance of my class within the dll:

Just to be clear, incase its my terminogloy:

MyDll d = new MyDll(); //for each TCP thread. I refer to this as
creating a new instance of my dll, but its a new instance of the class
inside

 

[Willy Denoyette [MVP]]

| Here is breif outline of the full app:
|
| A multi threaded TCP service (writting in C++) receives a number of
| messages. Each message spawns a new instance of a C# dll via COM.
| I have only tested the dll's being created via a Console app, but I
| suspect (and a little hope) that it works the same. My first instance of
| the C# dll will create the pool for this app, and subsequent dll's can
| use that pool.
|
Again, you don't create instances of C# DLL's, you can only create instances
of classes.
Here is what I think you are doing, a COM client (the C++ application) loads
the DLL and creates instance(s) of a class contained in the DLL. Each
message that arrives creates a new instance of a class, that instance
creates a db connection and starts a transaction, at the end of the
transaction, you close the connection.
Now, as I told you before, a pool is maintained per process (more precisely
per application domain) and has nothing to do with loading of DLL's. As long
as your application connects to the same db instance using the same
credentials, your process (application) uses the one and only pool. If
however, you change the credentials per connection, you will create a new
pool and you will incur the initial overhead for each new connection.
Now I would like to get some more questions answered;
1. What version of the framework are you running I assume v2.
2. Does your service create a thread per incoming message and do you create
an instance of the C# class in this thread?
3. How does your connection string looks like, here I mean, do you use
integrated security or sql security using explict credentials.
4. What's the interval between incoming TCP messages and what's the service
time of a transaction (duration of the connection).

Willy.

 

[Steven Blair]

Ok, thank you for the reply.

To answer your questions:

1. V.20
2. Yes
3. I am using Windows authentication:

Data Source=TS3DB04;Initial Catalog=VS_Account;Integrated
Security=True

4. We could be receiving multiple transactions per second. At least 20
messages per second. Its heavy processing, so thats why I need message
processing times down to minimum.
At the moment, using the leagcy software, message times are somewhere
between 30-80ms for the round trip. However, this is using Sql2000. The
software I am developing should be processing roughly the same time if
possible. There does seem to be a longer time penelty for connecting to
Sql 2005 initially.

Thanks for the continued support.

Steven

 

[Willy Denoyette [MVP]]

| Ok, thank you for the reply.
|
| To answer your questions:
|
| 1. V.20
| 2. Yes
| 3. I am using Windows authentication:
|
| Data Source=TS3DB04;Initial Catalog=VS_Account;Integrated
| Security=True
|
| 4. We could be receiving multiple transactions per second. At least 20
| messages per second. Its heavy processing, so thats why I need message
| processing times down to minimum.
| At the moment, using the leagcy software, message times are somewhere
| between 30-80ms for the round trip. However, this is using Sql2000. The
| software I am developing should be processing roughly the same time if
| possible. There does seem to be a longer time penelty for connecting to
| Sql 2005 initially.
|
| Thanks for the continued support.
|
| Steven
|
|
|
|
|

Ok, I see your problem.
In this scenario, the messages arrive at a too fast pace. If no free
connection entry is available in the pool, a new connection needs to be
created taking a large hit, this hit grows with the number of active
connections/threads the DB activity and the increased network overhead.
In your scenario, the first connection takes a minimum of ~150 msec., that
means that before this connection returns to the pool, a new message already
arrived, so a new connection has to be established, which takes another
large hit (> 100msec) and so on, until the first connection returns, which
is finally much later than 150 msec because of the increased system activity
(more connections more threads etc..). Hope you got the picture.

So in your case, where you must handle 20 messages per second, you can
hardly afford your transactions to take more than 50 msec. to execute.
Now, there are a number of option to handle this (in order of my
preference):
1. you can drop your C# component into a COM+ out-proc application (using
EnterpriseServices namespace classes), and use object pooling at the
component level. Each object instance in the pool (object pool) keeps the
connection open, so you don't pay the connection hit, unless a transaction
take more than an average, in which case you can tune the pool to hold more
objects.

2. you can change your design and share the (C#) object instance across
multiple threads, taking care of thread safety.

3. Warm up the connection pool, so that you have at least a number of
connection entries in the cache before your first message arrive.

IMO, if you want to guarantee a transaction rate of 20 or more per second,
your only option is #1. But of course as always, you will have to set-up a
test case and measure.


Willy.