Connection Pooling and Preparing Commands

[Joanna Carter [TeamB]]

Hi Folks

I am just trying to get my head around whether I can use a single
SQLConnection for the life of the application or whether I should create it
only when needed.

I want to create cached SQLCommand objects that are then prepared to reduce
subsequent execution time, but these can't be prepared unless their
Connection property has been set.

This then causes me to think that, if I create/dispose connections in a
"using" block, the vommands are going to keep a reference to the connection
alive anyway, so why not just keep the connection alive for the duration of
the application ?

If I make a connection for the first time, then prepare the commands, then
call Dispose on the connection, will the commands have to be prepared the
next time I instantiate the connection, using pooling ?

So I have two options :

1. Create connection, prepare commands on first call then keep them in a
list, only close connection when app closes

2. Create connection in "using" block, this then means I don't keep a list
of prepared commands and have to take the speed hit on running the commands
every time I get a new connection.

What is the better route ?

 

[ChrisM]

Hi Joanna,

I'm no expert, so don't quote me on this... but my understanding is that
it really depends on the number of users, the regularity of the calls, and
the importance of speedy responses.
That is if you have 100 users that are making infrequent calls to the
database then you'd be better off opening and closing connections as
required.
If you have 1-2 users that are making regular calls then it makes sense to
open a connection, and keep it open for the duration of the application.

Anything in-between I guess is a judgement call between the two...??

I'll watch this thread with interest as I'm never 100% sure of the right way
to go on this...

Cheers,

ChrisM.

 

[Joanna Carter [TeamB]]

"ChrisM" <[email protected]> a écrit dans le message de (e-mail address removed)...

| I'm no expert, so don't quote me on this...

Yes, we are all learning...; and how !

| That is if you have 100 users that are making infrequent calls to the
| database then you'd be better off opening and closing connections as
| required.

That is what I had planned on doing, but I understood that connection
pooling required you to not only open/close the connection but to
create/open/close/dispose it on every call.

I am reall trying to find out if creating a connection, and keeping the
connection alive but not necessarily opening/closing it until required, only
disposing it when the app closes, is a "proper" way to use a connection.

If I can keep the connection alive, but not necessarily open, for the length
of program execution (all day), then I could prepare commands and keep them
in a cache to save execution time there.

Joanna

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

Hi Folks

I am just trying to get my head around whether I can use a single
SQLConnection for the life of the application or whether I should create
it
only when needed.

IMO, you should create it at the last moment before connecting to the DB and
destroy it afterwards.

I want to create cached SQLCommand objects that are then prepared to
reduce
subsequent execution time, but these can't be prepared unless their
Connection property has been set.

How complex these Commands are? Most probably you do not need to call
Prepare. Please note that this is my personal opinion and not one respalded
with tests.
Also note that if you are calling a SP the Prepare has no impact as the
query is already "prepared" in the server.

Somewhere I read than SQL 2005 ( or 2000 ) had such a good query preparer
(not sure that is the correct term) than create the execution path of a
query was not longer a big problem, unless a really complex query was being
used.


IMO unless you are getting bad results you should stick with the create the
later, release the sooner.

 

[Guest]

When using connection pooling, closing the connection does not dispose the
object but returns it to the pool. Conversely, when instantiating a new
connection you are not really creating a new one but retrieving it from the
pool. This is highly optimized already, and it's better than keeping a
connection alive for the life of the application.

Ron

 

[=?ISO-8859-1?Q?G=F6ran_Andersson?=]

You don't gain anything by keeping a Connection object that is not
connected to the database. That object doesn't contain the actual
connection, it's just like an empty shell.

The Connection object gets a connection from the pool when you call the
Open method, and return it to the pool when you call the Close method.

(And, no, keeping a connected Connection objects is not a good idea either.)

 

[Willy Denoyette [MVP]]

| When using connection pooling, closing the connection does not dispose the
| object but returns it to the pool. Conversely, when instantiating a new
| connection you are not really creating a new one but retrieving it from
the
| pool. This is highly optimized already, and it's better than keeping a
| connection alive for the life of the application.
|

In principle, there is nothing wrong with keeping a single connection open
for the duration of an application (that's what the pool manager does after
all), I guess in Joanna's case it's the desired behavior. Keep in mind that
the Connection pool manager closes the connection with the database after
the connection has been idle for a certain amount of time (~6 minutes), so
if you only happen to issue commands every > 6 minutes you'll pay the
physical connection and athentication overhead for each open. Connection
pooling is great fo "server" style applications where a single application
(process) may have a varying number of DB connections open at a time.
"client" style applications that only have one single DB connection open at
a time do not take that much advantage of connection pooling.


Willy.

 

[=?ISO-8859-1?Q?G=F6ran_Andersson?=]

| When using connection pooling, closing the connection does not dispose the
| object but returns it to the pool. Conversely, when instantiating a new
| connection you are not really creating a new one but retrieving it from
the
| pool. This is highly optimized already, and it's better than keeping a
| connection alive for the life of the application.
|

In principle, there is nothing wrong with keeping a single connection open
for the duration of an application

For Windows applications, yes. For web applications, no.

 

[Willy Denoyette [MVP]]

That's what I said "Connection pooling is great fo "server" style
applications...".
Web applications are "server style".

Willy.

| Willy Denoyette [MVP] wrote:
| > | > | When using connection pooling, closing the connection does not dispose
the
| > | object but returns it to the pool. Conversely, when instantiating a
new
| > | connection you are not really creating a new one but retrieving it
from
| > the
| > | pool. This is highly optimized already, and it's better than keeping a
| > | connection alive for the life of the application.
| > |
| >
| > In principle, there is nothing wrong with keeping a single connection
open
| > for the duration of an application
|
| For Windows applications, yes. For web applications, no.
|
| > (that's what the pool manager does after
| > all), I guess in Joanna's case it's the desired behavior. Keep in mind
that
| > the Connection pool manager closes the connection with the database
after
| > the connection has been idle for a certain amount of time (~6 minutes),
so
| > if you only happen to issue commands every > 6 minutes you'll pay the
| > physical connection and athentication overhead for each open. Connection
| > pooling is great fo "server" style applications where a single
application
| > (process) may have a varying number of DB connections open at a time.
| > "client" style applications that only have one single DB connection open
at
| > a time do not take that much advantage of connection pooling.
| >
| >
| > Willy.
| >
| >
| >
| >