Connection Pooling and Preparing Commands

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

--
Joanna Carter [TeamB]
Consultant Software Engineer

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

"Joanna Carter [TeamB]" <joanna@not.for.spam> wrote in message
news:OmkS37glGHA.1640@TK2MSFTNGP02.phx.gbl...
> 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
>
> --
> Joanna Carter [TeamB]
> Consultant Software Engineer
>
>

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

"William (Bill) Vaughn" <billvaRemoveThis@nwlink.com> schreef in bericht
news:u%23wmAailGHA.3732@TK2MSFTNGP05.phx.gbl...
> 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.
> __________________________________
>
> "Joanna Carter [TeamB]" <joanna@not.for.spam> wrote in message
> news:OmkS37glGHA.1640@TK2MSFTNGP02.phx.gbl...
>> 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
>>
>> --
>> Joanna Carter [TeamB]
>> Consultant Software Engineer
>>
>>
>
>

"Carl Prothman" <carl@prothman.online.org> a écrit dans le message de news:
uhxjj6hlGHA.1344@TK2MSFTNGP03.phx.gbl...

| 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

--
Joanna Carter [TeamB]
Consultant Software Engineer

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

"Joanna Carter [TeamB]" <joanna@not.for.spam> wrote in message
news:uUbsLAqlGHA.2056@TK2MSFTNGP03.phx.gbl...
> "Carl Prothman" <carl@prothman.online.org> a écrit dans le message de
> news:
> uhxjj6hlGHA.1344@TK2MSFTNGP03.phx.gbl...
>
> | 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.
>>> Nope, you don't have to dispose the connection to release it to the
>>> pool, just Close it. Use Open to acquire a connection from the pool or
>>> create a new one.
>
> | 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 ?
>>> Nope, as long as you're using Parameters and not user-supplied values
>>> concatenated into the CRUD.

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

>>> The query plans cached in RAM on the server can still be accessed by
>>> other (subsequent) connections.

>
> Joanna
>
> --
> Joanna Carter [TeamB]
> Consultant Software Engineer
>
>

"William (Bill) Vaughn" <billvaRemoveThis@nwlink.com> a écrit dans le
message de news: u66WNt8lGHA.508@TK2MSFTNGP03.phx.gbl...

| 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

--
Joanna Carter [TeamB]
Consultant Software Engineer

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



"Joanna Carter [TeamB]" <joanna@not.for.spam> wrote in message news:O7QlI99lGHA.856@TK2MSFTNGP03.phx.gbl...

> "William (Bill) Vaughn" <billvaRemoveThis@nwlink.com> a crit dans le
> message de news: u66WNt8lGHA.508@TK2MSFTNGP03.phx.gbl...
>
> | 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
>
> --
> Joanna Carter [TeamB]
> Consultant Software Engineer
>
>