SqlConnection and pooling

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi;

We have a case where it is virtually impossible to know when to close a
connection because we may be about to make another query. So...

1) By any chance does SqlConnection implement pooling under the covers so
multiple calls to new SqlConnection()/sc.Close() are fast?

2) If I just hold on to a connection for a long time, is there any downside
to that - as long as it is one connection total for my app?

Also, when I pass a connection to SqlDataAdapter.SelectCommand = new
SqlCommand(select, connection), what happens to that connection? Am I
supposed to close it or close the SqlDataAdapter object?
 
1) By any chance does SqlConnection implement pooling under the covers so
multiple calls to new SqlConnection()/sc.Close() are fast?
Click to expand...

Yes it's quite fast.
2) If I just hold on to a connection for a long time, is there any
downside
to that - as long as it is one connection total for my app?
Click to expand...

Yes there are downsides, please read
http://codebetter.com/blogs/sahil.malik/archive/2005/01/30/48948.aspx
Also, when I pass a connection to SqlDataAdapter.SelectCommand = new
SqlCommand(select, connection), what happens to that connection? Am I
supposed to close it or close the SqlDataAdapter object?
Click to expand...

Nothing happens, just a new SqlCommand is created, SqlCommand.Connection
represents the connection you just passed in - nothing happened yet :-).
SqlDataAdapter does not need an open connection to have the above command
work. The SqlDataAdapter will take a close connection, open it, work with
it, and re-close it. Note that SqlConection is not the same as a physical
database connection. A SqlConnection is working with a real database
connection only when it's ConnectionState == ConnectionState.Open. For the
time when it's state is close, the framework will actually pool fewer open
connections between numerous SqlConnection instances. Here is a good reading
on how SqlClient connection pooling works under the scenes --
http://codebetter.com/blogs/sahil.malik/archive/2004/11/12/31798.aspx


HTH :)

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
 
David,

This newsgroup (and other dotNet newsgroups) is full of the advice to open
and close your connection as much as possible to get good connection pooling
by SQL server. Open and close is implicitly done by the dataadapter, if you
do not explicitly tell that. (Although if you open it by code you have to
close it by code).

The dataadapter is just an object that uses a bunch of other objects.
However you have to tell from some specific which, as by instance the
connection. By instance the datareader is something it takes standard.

I hope this helps,

Cor
 
Hi Dave,

This depends on the environment of your application.

If you're developing on an ASP.NET app, it is NOT recommended to share one
connection between the whole app. You need to open a connection wherever it
is needed, and close it as soon as it is used.

In a windows app, You can choose to share a connection or use multiple
connections. However, in any circumstances, the connection has to be closed
as soon as it is used.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
First, thank you everyone for the comments - that helps a lot.

I have a Windows client app and all DB access is in the UI thread. So it
looks like it might make sense to have a single connection object - but keep
it closed when not using it.
 
Sahil,
Single Connection Object == BAD !!!
Click to expand...
Can you enlighten us, according to the message from the OP (In a lot of
situations I agree with you in this). I assume that the OP means with
windows client = windows forms.

It is in my opinion not good (at least it is a not needed static situation),
however BAD !!! is a little bit to overdone, so probably I understand
something not.

Cor
 
Hi;

I read your blog, and I've done a lot of enterprise level coding over the
year. And if the connection object works as I think it does, I don't see why
it's bad if I have a client app (no web/server involved) and all DB access is
in a single thread.

What is the downside in this case? I understand not keeping it open, but if
closed, it's the same as a SqlDataAdapter which holds that connection for the
life of the adapter object.

???
 
When you say multiple readers - does that mean you should not pass the same
connection object to two SqlDataAdapter objects?

Also, what's MARS?

--
thanks - dave


Miha Markic said:
Single Connection Object && (Multithreading || multiple readers && !MARS) ==
BAD
:-)

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/


Sahil Malik said:
Single Connection Object == BAD !!!

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
Click to expand...
Click to expand...
 
LOL :-)


--

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------
---------------

Miha Markic said:
Single Connection Object && (Multithreading || multiple readers && !MARS) ==
BAD
:-)

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/


Sahil Malik said:
Single Connection Object == BAD !!!

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
--------------------------------------------------------------------------
Click to expand...
--
------------------------------------------------------------------------- -
-- close
a
Click to expand...
Click to expand...
 
David,

You will have all kinds of problems in the long run with one SqlConnection
instance. Read here -
http://codebetter.com/blogs/sahil.malik/archive/2005/01/30/48948.aspx


MARS is "Multiple Active Resultsets" - i.e. you can maintain two or more
(there is an upper limit of 10 or 11) concurrent active resultsets
maintained on the same connection. This needs SQL2k5 to work. On the client
side you can work with either ADO.NET 2.0, or latest versions of OleDB/ODBC.
You should be very careful when using MARS, lots of potholes to watch out
for. There is a good discussion of that in my book (chap 11), but I can tell
you more about it here if you are interested.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------
---------------

David Thielen said:
When you say multiple readers - does that mean you should not pass the same
connection object to two SqlDataAdapter objects?

Also, what's MARS?

--
thanks - dave


Miha Markic said:
Single Connection Object && (Multithreading || multiple readers && !MARS) ==
BAD
:-)

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/


Sahil Malik said:
Single Connection Object == BAD !!!

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
Click to expand...
-------------------------------------------------------------------------- --
---------------

First, thank you everyone for the comments - that helps a lot.

I have a Windows client app and all DB access is in the UI thread. So it
looks like it might make sense to have a single connection object - but
keep
it closed when not using it.

--
thanks - dave


:

1) By any chance does SqlConnection implement pooling under the
covers
so
multiple calls to new SqlConnection()/sc.Close() are fast?

Yes it's quite fast.

2) If I just hold on to a connection for a long time, is there any
downside
to that - as long as it is one connection total for my app?

Yes there are downsides, please read
http://codebetter.com/blogs/sahil.malik/archive/2005/01/30/48948.aspx

Also, when I pass a connection to SqlDataAdapter.SelectCommand = new
SqlCommand(select, connection), what happens to that connection? Am I
supposed to close it or close the SqlDataAdapter object?

Nothing happens, just a new SqlCommand is created,
SqlCommand.Connection
represents the connection you just passed in - nothing happened yet
:-).
SqlDataAdapter does not need an open connection to have the above
command
work. The SqlDataAdapter will take a close connection, open it, work
with
it, and re-close it. Note that SqlConection is not the same as a
physical
database connection. A SqlConnection is working with a real database
connection only when it's ConnectionState == ConnectionState.Open. For
the
time when it's state is close, the framework will actually pool fewer
open
connections between numerous SqlConnection instances. Here is a good
reading
on how SqlClient connection pooling works under the scenes --
http://codebetter.com/blogs/sahil.malik/archive/2004/11/12/31798.aspx


HTH :)

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
Click to expand...

------------------------------------------------------------------------- -
--


Hi;

We have a case where it is virtually impossible to know when to close
a
connection because we may be about to make another query. So...

1) By any chance does SqlConnection implement pooling under the
covers
so
multiple calls to new SqlConnection()/sc.Close() are fast?

2) If I just hold on to a connection for a long time, is there any
downside
to that - as long as it is one connection total for my app?

Also, when I pass a connection to SqlDataAdapter.SelectCommand = new
SqlCommand(select, connection), what happens to that connection? Am I
supposed to close it or close the SqlDataAdapter object?
Click to expand...
Click to expand...
Click to expand...
 
What?

"In a windows app, You can choose to share a connection or use multiple
connections. However, in any circumstances, the connection has to be
closed
as soon as it is used."
Click to expand...
Hardly. Lots of designs leverage an on-going server-side state that's only
possible/practical in Windows forms.
The only reason to keep closing and reopening a Windows Forms client
connection is to conserve server-side connection resources.
While a single connection/application might not make sense (shared between
forms), keeping the connection open as long as needed does (make sense).



--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
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.
__________________________________
 
David,

I am glad you found my blog useful.

There are three main disadvantages of having a global SqlConnection
instance - even if you close it.

a) Threading issues - you don't seem to have this, but it's an issue
nonetheless
b) Locking yourself in a non-server architecture i.e. a future vulnerable
architecture (your app demands will change eventually).
c) Inability to run multiple parallel queries - even on seperate
transactions.

Also, given that it is zero extra effort in recreating SqlConnection
objects, the performance isn't much worse, why not just recreate
SqlConnection instances RATHER THAN having to maintain a global
SqlConnection object? Almost like you are doing more work for less gain.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
 
Why is server side state not possible in web forms? And why sould windows
forms facilitate serverside state anymore than web? Albeit the IIS session
just acts as a proxy to your windows forms code - the state maintainance is
still possible. And if that is a bad idea in Web, why is that a good idea in
Windows?

And even if you did close a Windows Forms connection, with pooling enabled,
the database and SqlClient is still maintaining an open connection *after*
you have called SqlConnection.Close. The only difference is that your
database connection can now be pooled with another SqlConnection instance.
SqlConnection instances should be kept open for as little time period as
possible.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
 
David,
What is the downside in this case? I understand not keeping it open, but
if
closed, it's the same as a SqlDataAdapter which holds that connection for
Click to expand...
In my opinion do you show it with this exactly as it is. "holds the
reference to the not released connection object". However everybody should
understand that you mean that.

Cor
 
Bill,

Your answer does match the message from Kevin. He told in my opinion the
same as you.

Are you repeating Kevins answer?

(Because of the "What" have I the idea that you did read it as you thought
that it was written and not as it was)

:-)

Cor
 
Sahil,
This is in the message from Kevin
In a windows app, You can choose to share a connection or use multiple
connections. However, in any circumstances, the connection has to be
Click to expand...

A single thread application (and because Kevin is not speaking about
multithreading is that for me default) can only do serialised an action. As
sample what in my opinion Kevin wrote.

Connection conn1 = new connection
Connection conn2 = new connection

conn1.open
do stuff whatever
conn1.close
conn2.open
do stuff whatever
conn2.close

Not any problem in my opinion and if the message from Bill is in conflict
with this than I disagree with Bill (To repeat for a single thread
windowsform application).

Cor
 
Back
Top