Disconnected vs .. um .. connected?

[Steven Nagy]

I know that .NET is based on a disconnected architecture, but I can't
conceive of why continually opening and closing a connection would be
faster than leaving a connection open.
So I ran a test and came up with a result of exactly the same times!

50 rows selected into a datareader, in a loop of 50
One opens and closes the connection in each iteration of the loop, the
other just opens before the loop starts, then closes after the loop is
finished.

62.5 milliseconds for both

So why is this, and which is best? I know MS preaches disconnected, but
I need to know why, then I am converted.

Thanks

[Sayed Ibrahim Hashimi]

The argument for disconnected vs connected is not over performance, it is
about scalability. If you're writing applications that require a live
connection to the DB, then you'll run into issues when the application is in
use by many users concurrently. Issues not only like physical resources on
the server, but possibly licencing issues. There are other reasons why
disconnected is a better choice as well, but I think scalability is probably
the key factor.

Sayed Ibrahim Hashimi
www.sedodream.com

"Steven Nagy" wrote:

I know that .NET is based on a disconnected architecture, but I can't
conceive of why continually opening and closing a connection would be
faster than leaving a connection open.
So I ran a test and came up with a result of exactly the same times!

50 rows selected into a datareader, in a loop of 50
One opens and closes the connection in each iteration of the loop, the
other just opens before the loop starts, then closes after the loop is
finished.

62.5 milliseconds for both

So why is this, and which is best? I know MS preaches disconnected, but
I need to know why, then I am converted.

Thanks

[Ant]

Open connections co$t. The fewer open, the cheaper. That's my two cents.

Ant

"Sayed Ibrahim Hashimi" wrote:

The argument for disconnected vs connected is not over performance, it is
about scalability. If you're writing applications that require a live
connection to the DB, then you'll run into issues when the application is in
use by many users concurrently. Issues not only like physical resources on
the server, but possibly licencing issues. There are other reasons why
disconnected is a better choice as well, but I think scalability is probably
the key factor.

Sayed Ibrahim Hashimi
www.sedodream.com

"Steven Nagy" wrote:

I know that .NET is based on a disconnected architecture, but I can't
conceive of why continually opening and closing a connection would be
faster than leaving a connection open.
So I ran a test and came up with a result of exactly the same times!

50 rows selected into a datareader, in a loop of 50
One opens and closes the connection in each iteration of the loop, the
other just opens before the loop starts, then closes after the loop is
finished.

62.5 milliseconds for both

So why is this, and which is best? I know MS preaches disconnected, but
I need to know why, then I am converted.

Thanks

[Steven Nagy]

Yeah, but WHY?

I already know that people don't like them, I need to know the why.

I just ran another test with an sql statement that utilised a simple
inner join, 2 where conditions, and an order by.
I called this sql statement with a data reader, returning 100 rows. I
called it in a loop 200 times.
I did it with the connection open all the time.
Then I did the same thing opening and closing it for every call to the
database.

Response times are practically identical.

So, WHY is this so, and WHY is it better to have fewer open
connections?
What if the environment is controlled and I can guarantee less than 5
connections?

Thanks.

[Scott C]

Steven Nagy wrote:

I just ran another test with an sql statement that utilised a simple
inner join, 2 where conditions, and an order by.
I called this sql statement with a data reader, returning 100 rows. I
called it in a loop 200 times.
I did it with the connection open all the time.
Then I did the same thing opening and closing it for every call to the
database.

Not sure, but I think SqlConnection uses connection pooling-- it keeps
an "available" connection open (without you knowing it) and reuses it if
the conn string is the same. That's why you're seeing the same results
I suspect-- the connection is never (really) closed.

Maybe you could convince yourself (and me) by changing the connection
string (use a different user?) each time and see if that has an impact.

Scott

[Hans Kesting]

> Steven Nagy wrote:

I just ran another test with an sql statement that utilised a simple
inner join, 2 where conditions, and an order by.
I called this sql statement with a data reader, returning 100 rows. I
called it in a loop 200 times.
I did it with the connection open all the time.
Then I did the same thing opening and closing it for every call to the
database.

Not sure, but I think SqlConnection uses connection pooling-- it keeps an
"available" connection open (without you knowing it) and reuses it if the
conn string is the same. That's why you're seeing the same results I
suspect-- the connection is never (really) closed.

Maybe you could convince yourself (and me) by changing the connection string
(use a different user?) each time and see if that has an impact.

Scott

Yes, connection-pooling is used. For this to work, the
connections-strings should be exactly the same, not just "similar".
Switching the various arguments around will count as "different".

The two testcases (keep a single connection open versus opening and
closing on every iteration) in reality both use just a single open
connection. That's why the results are the same.

You could also read the results the other way: if there is no
difference, why *not* close the connection as soon as you are done with
it? For a winform application I don't think there will be much
difference, for a webapplication you will *need* to close the
connection as soon as possible (as there are multiple concurrent
users).

Hans Kesting

[AAJ]

Hi all

The disconnected/connected question is something I've been pondering as
well. I understand the scalability and cost issues but I wonder if there is
a compromise being made elsewhere.

It struck me that one of the main benefits of client/server setups is the
data is stored safely immediately, but if apps are designed to run
disconnected, then there would be greater potential for loss of data.
Perhaps due to loss of power, crashes and other unforeseen events ( I once
accidentally stood on a multiway adapter ON/OFF switch and turned off 4
development machines), where as writing the data straight to the Server
Database means once entered, the data will be guaranteed.

Also (this isn't a dig at MS, its just a simple newbies thought), it 'seems'
much more complicated then working with the old ADO recordsets.
DataAdapters, Datasets, Datsets looking for changes, Commandbuilders, Back
to DataAdapters etc...

anyway, just my thoughts, I'm sure its been discussed long and hard by
cleverer people than me 8-)

Andy
"Hans Kesting" <ne***********@spamgourmet.com> wrote in message
news:mn***********************@spamgourmet.com...

Steven Nagy wrote:

I just ran another test with an sql statement that utilised a simple
inner join, 2 where conditions, and an order by.
I called this sql statement with a data reader, returning 100 rows. I
called it in a loop 200 times.
I did it with the connection open all the time.
Then I did the same thing opening and closing it for every call to the
database.

Not sure, but I think SqlConnection uses connection pooling-- it keeps an
"available" connection open (without you knowing it) and reuses it if the
conn string is the same. That's why you're seeing the same results I
suspect-- the connection is never (really) closed.

Maybe you could convince yourself (and me) by changing the connection
string (use a different user?) each time and see if that has an impact.

Scott

Yes, connection-pooling is used. For this to work, the connections-strings
should be exactly the same, not just "similar". Switching the various
arguments around will count as "different".

The two testcases (keep a single connection open versus opening and
closing on every iteration) in reality both use just a single open
connection. That's why the results are the same.

You could also read the results the other way: if there is no difference,
why *not* close the connection as soon as you are done with it? For a
winform application I don't think there will be much difference, for a
webapplication you will *need* to close the connection as soon as possible
(as there are multiple concurrent users).

Hans Kesting

[Steven Nagy]

Thanks all.

I was not aware that that's what connection pooling was all about.

Makes a bit more sense to me now.

But my question now is this:
If connection pooling keeps an active connection anyway, whats the
point of closing my connection in code?
Is it just a matter of cleaning up my objects?

[Scott C]

Steven Nagy wrote:

But my question now is this:
If connection pooling keeps an active connection anyway, whats the
point of closing my connection in code?
Is it just a matter of cleaning up my objects?

I just checked out Roeder's .NET Reflector for SqlConnection.Close().
[Dispose() just calls Close()] Seems that there's a _lot_ that goes on
in close. It closes any DataReader, "unprepares" SqlCommands, and rolls
back a failed(?) transaction. ... and that's just what I got from a
cursory glance.

The moral of the story is, if a method has a "Close()" or implements
IDisposable, always call it, even if it seems unnecessary (see
MemoryStream).

Scott

[Hans Kesting]

> Thanks all.

I was not aware that that's what connection pooling was all about.

Makes a bit more sense to me now.

But my question now is this:
If connection pooling keeps an active connection anyway, whats the
point of closing my connection in code?
Is it just a matter of cleaning up my objects?

When you "close" the connection, you signal that it can be returned to
the pool (plus everything that Scott C said).

Hans Kesting