ADO.NET connections

  • Thread starter Thread starter PRP
  • Start date Start date
P

PRP

Hi,

Our DBA has complained about the large number of connections from the
aspnet_wp process.

We have multiple web applications deployed in different virtual
directories. I read that the way ADO.NET in-built connection pool works
is per app domain. So in a scenario where I am using single connection
string (from web.config) and I am closing all connections as
recommended, is it correct to assume that ideal number of connections
from aspnet_wp process should be equal to the number of Asp.net web
applications that I configured?

Thanks for your help.
 
I would suggest you implement singleton pattern for having just single
connection thoughout the application.

You have said that you are using single connnection string but there can
be
many connections open.So best practice is to have single connection open
through out the application by implementing singleton pattern
Click to expand...

I couldn't disagree more!

Under no circumstances do that! A singleton pattern is not appropriate at
all for connections. IMO, you should only use singletons when you really
need to, e.g. for.queuing or synchronisation. Singletons can cause
concurrency/multithreading issues as well as performance bottlenecks,
especially with regards to database connections.

IMO, you should instantiate a connection only when needed, and destroy it as
soon as you're finished with it. ADO.net uses connection pooling to minimise
the creation of new connections. Keep the duration of database connections
as short as possible so that the connection is returned to the connection
pool where it can be used by the next database transaction.

A singleton pattern, by definition, uses static variables - like Highlander,
there can be only one - that's what 'singleton' means, so every user of the
website uses the same connection all the time - best hope you only have one
concurrent user of your site... In addition, a singleton pattern connection
object would not be scalable up to a web gardening or web farming
architecture.

http://www.velocityreviews.com/forums/t63537-singleton-pattern-in-aspnet-application.html
 
Thanks for the replies.
IMO, you should instantiate a connection only when needed, and destroy it as
soon as you're finished with it. ADO.net uses connection pooling to minimise
the creation of new connections. Keep the duration of database connections
as short as possible so that the connection is returned to the connection
pool where it can be used by the next database transaction.
Click to expand...

This is exactly what we are doing in our applications. But now we are
experiencing lot of connection being open from aspnet_wp in our Oracle
database. While we identify/correct the possible long running
tasks/programming mistakes that can cause this issue, what I should be
saying to DBA as expected when he queries for v$sessions. That is
where I am confused. If I have four web apps running in IIS, connecting
to Oracle with same connection string, the ideal number of connections
at any point should be four, am I right??
 
This is exactly what we are doing in our applications.
Click to expand...

Glad to hear it.
But now we are experiencing lot of connection being open from aspnet_wp
in our Oracle database.
Click to expand...

I'd imagine so - that's the whole point. Lots of very short connections as
opposed to a few long connections or, even worse, one persistent connection.
While we identify/correct the possible long running tasks/programming
mistakes
that can cause this issue, what I should be saying to DBA as expected when
he
queries for v$sessions.
Click to expand...

I'd tell him that this is to be expected - what is his problem, AAMOI...?
That is where I am confused. If I have four web apps running in IIS,
connecting
to Oracle with same connection string, the ideal number of connections at
any
point should be four, am I right??
Click to expand...

Er, no! The number of *connection pools* should be four.

When a connection is first opened, a connection pool is created based on an
exact matching algorithm that associates the pool with the connection string
in the connection. Each connection pool is associated with a distinct
connection string. When a new connection is opened, if the connection string
is not an exact match to an existing pool, a new pool is created.
Connections are pooled per process, per application domain, per connection
string and when using integrated security, per Windows identity.

When a pool is created, multiple connection objects are created and added to
the pool up to the minimum pool size value. Connections are added to the
pool as needed, up to the maximum pool size specified (default = 100).
Connections are released back into the pool when they are closed or
disposed.

When a SqlConnection object is requested, it is obtained from the pool if a
usable connection is available. To be usable, a connection must be unused,
have a matching transaction context or be unassociated with any transaction
context, and have a valid link to the server. I'd imagine this is the same
for Oracle, but don't know - sorry...
The connection pooler satisfies requests for connections by reallocating
connections as they are released back into the pool. If the maximum pool
size has been reached and no usable connection is available, the request is
queued. The pooler then tries to reclaim any connections until the timeout
is reached (default = 15 seconds). If the pooler cannot satisfy the request
before the connection times out, an exception is thrown.
 
While we identify/correct the possible long running tasks/programming
I'd tell him that this is to be expected - what is his problem, AAMOI...?
Click to expand...

Sorry,I missed the important part; he was talking about connections
with status "INACTIVE" in v$sessions.


When a pool is created, multiple connection objects are created and added to
the pool up to the minimum pool size value. Connections are added to the
pool as needed, up to the maximum pool size specified (default = 100).
Connections are released back into the pool when they are closed or
disposed.
Click to expand...

Thanks for the detailed explanation, and clearing my doubts.
but at what moment in this process oracle gives the status as
"Inactive"?
 
Sorry,I missed the important part; he was talking about connections
with status "INACTIVE" in v$sessions.
Ah...


Thanks for the detailed explanation, and clearing my doubts.
but at what moment in this process oracle gives the status as
"Inactive"?
Click to expand...

Er, when they're inactive i.e. not being used for anything... Each
connection pool has a minimum number of connections which are created when
the pool is first created.
 
Thanks for that. Good read.


Peter

Mark Rae said:
This is exactly what we are doing in our applications.
Click to expand...

Glad to hear it.
But now we are experiencing lot of connection being open from aspnet_wp
in our Oracle database.
Click to expand...

I'd imagine so - that's the whole point. Lots of very short connections as
opposed to a few long connections or, even worse, one persistent
connection.
While we identify/correct the possible long running tasks/programming
mistakes
that can cause this issue, what I should be saying to DBA as expected
when he
queries for v$sessions.
Click to expand...

I'd tell him that this is to be expected - what is his problem, AAMOI...?
That is where I am confused. If I have four web apps running in IIS,
connecting
to Oracle with same connection string, the ideal number of connections at
any
point should be four, am I right??
Click to expand...

Er, no! The number of *connection pools* should be four.

When a connection is first opened, a connection pool is created based on
an exact matching algorithm that associates the pool with the connection
string in the connection. Each connection pool is associated with a
distinct connection string. When a new connection is opened, if the
connection string is not an exact match to an existing pool, a new pool is
created. Connections are pooled per process, per application domain, per
connection string and when using integrated security, per Windows
identity.

When a pool is created, multiple connection objects are created and added
to the pool up to the minimum pool size value. Connections are added to
the pool as needed, up to the maximum pool size specified (default = 100).
Connections are released back into the pool when they are closed or
disposed.

When a SqlConnection object is requested, it is obtained from the pool if
a usable connection is available. To be usable, a connection must be
unused, have a matching transaction context or be unassociated with any
transaction context, and have a valid link to the server. I'd imagine this
is the same for Oracle, but don't know - sorry...
The connection pooler satisfies requests for connections by reallocating
connections as they are released back into the pool. If the maximum pool
size has been reached and no usable connection is available, the request
is queued. The pooler then tries to reclaim any connections until the
timeout is reached (default = 15 seconds). If the pooler cannot satisfy
the request before the connection times out, an exception is thrown.
[/QUOTE]
 
Er, when they're inactive i.e. not being used for anything... Each
connection pool has a minimum number of connections which are created when
the pool is first created.
Click to expand...

Really? I have never seen this before, only connections open on demand.


Connection pooling works by keeping connections "established" behind
the scenes so you're going to see these Inactive sessions in v$session
but they're normal. This is the whole point of connection pooling since
it eliminates the overhead of establishing a new connection every time
you make a database call. You're also going to see at least 4 if each
of the apps has been hit at least once (since each app runs in a
separate process and connection pools are per process) but you're going
to see much more under normal use.

The connection pool mechanism in a nutshell:

1.) MyApp calls the Connection.Open().

2.) The library checks to see if there is an established but idle (not
in use by another thread in this same app) connection in the pool with
the exact same connection string and is not older than the time span
specified in the Connection Lifetime parameter of the connection string
(This is 0 by default, meaning maximum connection lifetime). If so, it
returns this connection, otherwise it establishes a new one.

3.) After you call connection.Close() the connection is still in the
pool and remains established, available for the next call to
Connection.Open()
 
Mark said:
That's true, but why would anyone do that...?
Click to expand...

I would ask the opposite. What good is this number unless your
application has a constant number of connections (how often is this
the case)?
 
Back
Top