Connection Pooling and Preparing Commands

[Joanna Carter [TeamB]]

Hi Folks

..NET 2.0 and SQLServer2005

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 ?

Joanna

 

[William \(Bill\) Vaughn]

While Carl's suggestions have merit, I'm of the opinion that it's perfectly
okay to open a connection and leave it open for the life of the
application--assuming you're not using ASP. Persistent connections give you
a way to maintain and mange state on the server including server-side
cursors, temp tables, SET variables and more. SQL Server can support
hundreds to thousands of users (on a single system). Each connect open
consumes workstation, network and server resources. Doing this each time you
need to query is more expensive than simply leaving the connection open. You
do, however, have to make sure that the connection's channel is cleared by
properly closing DataReaders as a connection can only support one operation
at a time. (Yes you can try MARS but this does not fix that problem--just
delays the time when it fails).

The connect, query, disconnect approach is essential for ASP--not for
client/server.

HTH

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Cor Ligthert [MVP]]

Bill,

I (as forever with full respect) have an opposite opinion than you.

In my opinion is it better to open and close connections (as everything) as
thigh together in the code as possible. For me are technical reason not
only important, in my idea does it make the code easier to review and
maintain.

However as forever just my thought,

Cor

 

[Joanna Carter [TeamB]]

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

| It's best practice to use Connection Pooling. So open and close your
SqlConnection only when it's needed. Make sure to close the SqlConnection,
otherwise it won't go back into the Connection Pool.

I wasn't planning on leaving the connection open, my query was that I was
under the impression that connection pooling only worked if you
created/disposed the connection, rather than just open/close.

| Are your SqlCommands using in-line SQL or are they calling Stored
Procedures? It's best practice to use Stored Procedures. If the SQL is
slow, there are a lot of tricks to speeding up it up depending on the query,
schema, etc.

We were not going to bother with stored procs as the SQL we will be using
will be mainly single table CRUD operations; I don't think the performance
would be that much different, would it ?

| I would not worry about creating prepared commands other than having the
ADO.NET code in place (e.g. data class).

My question here is : if I have prepared a command and then I close the
connection, when I opern the connection again, will the same command still
be prepared or will it need preparing again ?

Joanna

 

[William \(Bill\) Vaughn]

See >>>

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

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

| It's best practice to use Connection Pooling. So open and close your
SqlConnection only when it's needed. Make sure to close the SqlConnection,
otherwise it won't go back into the Connection Pool.

I wasn't planning on leaving the connection open, my query was that I was
under the impression that connection pooling only worked if you
created/disposed the connection, rather than just open/close.
| Are your SqlCommands using in-line SQL or are they calling Stored
Procedures? It's best practice to use Stored Procedures. If the SQL is
slow, there are a lot of tricks to speeding up it up depending on the
query,
schema, etc.

We were not going to bother with stored procs as the SQL we will be using
will be mainly single table CRUD operations; I don't think the performance
would be that much different, would it ?

 

[Joanna Carter [TeamB]]

"William (Bill) Vaughn" <[email protected]> a écrit dans le
message de news: (e-mail address removed)...

| See >>>

Thanks Bill. Could I just confirm that, for a desktop app, it is OK to
create a connection that will live all day, being opened and closed during
that time when required ?

Joanna

 

[Carl Prothman]

Joanna,
Yes, you can create a connection object at the start of your application, and then only open and close it when you need to get or change data in your database.

--

Thanks,
Carl Prothman
Microsoft ASP.NET MVP
http://www.CarlProthman.NET