473,216 Members | 1,183 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,216 software developers and data experts.

Disconnected vs .. um .. connected?

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

Feb 9 '06 #1
9 1798
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

Feb 9 '06 #2
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

Feb 9 '06 #3
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.

Feb 9 '06 #4
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
Feb 9 '06 #5
> 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
Feb 9 '06 #6
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

Feb 9 '06 #7
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?

Feb 9 '06 #8
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
Feb 10 '06 #9
> 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
Feb 10 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: James Yang | last post by:
Hi, I am using Sockets to connect to a remote computer and send data using Socket.Send() and receive using Socket.Receive() (block mode) . for somereason tho, when the client disconnects the...
6
by: RMG | last post by:
I use VS.net 2003 and VSS 6a. Whilst working on the network the integration between VS.net and VSS works perfectly. But when I open a project whilst disconnected from the network I get some...
2
by: giloosh99 | last post by:
Hello, Im grabbing tables via VB code using visual foxpro ODBC drives. The tables directory is in a mapped network drive. The code works fine and does the job, however if the computer is idle for...
3
by: light_wt | last post by:
Hi, From my understanding DataSet is used for disconnected data access in ADO.NET. However, I haven’t come across an example to use continuous data access with ADO.NET using C# . Is...
4
by: Steve Le Monnier | last post by:
The ADO.NET DataSet is idea for application development, especially if you need disconnected data. DataReader objects are great in the connected environment but are forward only. What do you do...
0
by: Cat | last post by:
Hello. Is it POSSIBLE to get notified when the machine is connected to the internet or disconnected from the internet? I mean, using .NET class not using Windows API. Other than pinging a website...
3
by: AC | last post by:
Running VS.NET 2003 Enterprise Arch on WinXP Pro SP1 with a P4-2.4Ghz, 760MB+ RAM, and 10+GB free disk space. Laptop is part of a domain. When working on a web project connected at the office,...
1
by: dejavue82 | last post by:
Hello fellow programmers! My question concerns the scalability of database access. In coldfusion (or php for that matter) for example, there seems to be only the connected model for accessing a...
2
by: Gabriel | last post by:
Hello, I'm looking for documentation with "Best Practice" for ASP.NET application In which case use Connected or Disconnected mode Typed dataset or not ? I didn'd find anything pertinent...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.