Making a database connection global

[Terry Jolly]

You're wrong - that I think anybody is wrong - I merely question the logic -
and give anyone the abiltiy (or rtaher ask anyone) to explain their
position. It's one thing to say I'm wrong, give no answer to a different way
and become upset if your way (which in not explained) is questioned.

I'm listening to every point of view, thinking it through and may question
that point of view for (my better) understanding. Don't be afraid to have
you view questioned, it's not saying you are wrong.

 

[Terry Jolly]

No one has given a different view yet, accept to say I'm wrong and I'm
leaving the conncection open, which is not true. So, I asked if opening 30+
connections made more sense than one connection if in both cases the
connection would be opened the same amount of time (which no-one yas yet
answered). I really would like to hear another point of view. I have not
discounted anything anyone has said. Someone did say:

my suggestion is having one class that just handles commands to
database.

your other 30+ classes would just prepare commands and pass them for
execution. So you will have central place for working with
SQLConnection object

Which I like and am (have been) diagraph/mapping out to see if this would
work. Don't be offended about asking to clarity you position better. It
doesn't mean I think your wrong.

 

[Terry Jolly]

Thank you...I will re-review connection pooling. I thought I had read
(earlier) there was a limited amount of connections available, which made me
think what I was (thinkning about) doing made more sense. The connection
would NOT be open longer in the method I was suggesting. Please do not
mis-understand I am not trying to challneging you. I'm just trying to
understand why 30 connections opened in a given time would be better than 1
connection. Again the one connection would not be opened longer than if each
class has it's own connection. Plus if you have 10 hits to the site then 10
* 30 possible connectios?

Basically, I would open one global connection, perform any work with the 30
classes (tables) and immedtaley close the global connection. If you think
this is a bad idea, then what would you do differently?

If you think i'm an idiot, thats fine, enlighted me.

 

[=?ISO-8859-1?Q?G=F6ran_Andersson?=]

Ok, I'll try without sarcasm.

The web server is built to process several web pages simultaneously
using mutliple threads. The database is built to process several queries
simultaneously using multiple sessions. If you use only one connection
in your application, that will become the bottle neck of the
application. Whatever you do, it will always boil down to the fact that
only one thread at a time can access the database, and all the other
threads have to wait their turn.

Open a connection for each page, and use that for the database
operations in the page. When you close the connection it will be
returned to the connection pool. Connections are only costly when you
create them the first time. When there is a connection in the connection
pool that can be reused, opening it is much faster.

 

[Bob Lehmann]

Look at it this way....

Which do you think is quicker - 30 people lined up at one ticket booth to
pay movie admission, or 30 people lined up at 30 ticket booths.

As a bonus, with 30 ticket booths, the attendants knowin advance which movie
you want to see and have your ticket ready.

Bob Lehmann

 

[Bob Lehmann]

my suggestion is having one class that just handles commands to

database.

You misunderstand.

Having one class to handle *many* connection *instances* is correct. Having
one class, with one single *shared* connection, is bad.

Bob Lehmann

 

[Mark Rae]

Mark Rae even answered him using my own words, so there wasn't much I
could add to that.

LOL! Yes, I should have credited you for that - sorry...

 

[Mark Rae]

If you use only one connection in your application, that will become the
bottle neck of the application. Whatever you do, it will always boil down
to the fact that only one thread at a time can access the database, and
all the other threads have to wait their turn.

I think we're all wasting our time with this one...

 

[Terry Jolly]

Thanks for explanation - makes sense!

Look at it this way....

Which do you think is quicker - 30 people lined up at one ticket booth to
pay movie admission, or 30 people lined up at 30 ticket booths.

As a bonus, with 30 ticket booths, the attendants knowin advance which
movie
you want to see and have your ticket ready.

Bob Lehmann

 

[Terry Jolly]

I had always planned to open a connection for each web page. That was not
the issue. The one web page may open several classes (tables) then do what's
necessary then close the tables and the connection for the page. Each and
every web page opens it's own connection, yet several tables for the one web
page (which are in classes)! If each table opened a connection per web page
and there are many tables, again less say 30, that would be 30 connections
per web page. The connection pool would soon run short. This is what I have
been asking and everyone seems to think I talking about one connection for
all web pages, which was never the case. The global connection was only
global for the one web page, not through the entire web site!

 

[Juan T. Llibre]

You can always close connections in a web page, even if there's 1,000
connections in it, as soon as you're done retrieving the data you need to request.

The number of connections in a page doesn't have a bearing
on how soon you shoud close a connection you've opened.

The answer is always the same : as soon as you're done retrieving/displaying the data.

 

[Terry Jolly]

I totally agree! Once the data is retrieved the table and connection is
closed.

One web pages has ONE class, the business class that opens the connection
(public) then the business class may open (depending on what's needed)
several tables (at the same time) (becuase a join will not work -- later
discussion), retrieves the data, closes the tables and then closes the
connection.

Are you saying you never open multiple tables on the same connection?

 

[Juan T. Llibre]

re:

Are you saying you never open multiple tables on the same connection?

The number of tables doesn't matter.

The connections to the databases, whether to one table or to many,
should always be closed as soon as you're done retrieving/displaying the data.

 

[=?ISO-8859-1?Q?G=F6ran_Andersson?=]

Well, in that case you are on the track.

You wrote in your original post that you intended to use a static
database connection. Unless you also make it ThreadStatic, it would be
shared by all threads that use the class. That is why everyone thought
that you wanted to use one single connection for all requests.

I've built a data access library that used threadstatic connections, and
I advice against it. Create the connection at one level, pass it along
to any method that needs to use it, and close it at the same level as
you created it. I use an application specific wrapper class around a
general database handler class. The database calls go in classes that
represent the objects in the database. The code in the page ends up
looking something like:

List<SomeObject> someThings;

using (Db db = new Db()) {
someThings = SomeObect.GetAll();
SomeObject.DoSomeUpdate();
}
foreach (SomeObject something in someThings) {
...use something
}

 

[=?ISO-8859-1?Q?G=F6ran_Andersson?=]

List<SomeObject> someThings;

using (Db db = new Db()) {
someThings = SomeObect.GetAll();
SomeObject.DoSomeUpdate();
}
foreach (SomeObject something in someThings) {
...use something
}

As I send the database object along to any method that uses it, it's of
course like this:

List<SomeObject> someThings;

using (Db db = new Db()) {
someThings = SomeObect.GetAll(db);
SomeObject.DoSomeUpdate(db);
}
foreach (SomeObject something in someThings) {
...use something
}

 

[Damien]

I totally agree! Once the data is retrieved the table and connection is
closed.

One web pages has ONE class, the business class that opens the connection
(public) then the business class may open (depending on what's needed)
several tables (at the same time) (becuase a join will not work -- later
discussion), retrieves the data, closes the tables and then closes the
connection.

Are you saying you never open multiple tables on the same connection?

Hi Terry,

I think you confused a lot of people with your use of the word
"global". Global tends to indicate that there is one and only one in
the entire application. Instead, I think we're now gathering that
during a single page request, you're opening a connection, performing
various actions against one or more tables, and then closing the
connection. If this is the case, then it's probably spot on.

The various connection options I can think of are:

1) One shared, global connection, which anyone wanting to use it must
synchronize access to,
2) One connection per session (which if multplie requests per session
are possible, must be synchronized)
3) One connection per request
4) One connection per database command

I think you're looking at option 3, which I believe to be quite
reasonable. My own preference is for 4, but that's because it's easier
to do in my Database Objects Generator (which generates classes to
interact with the db). 3 would probably be better if you needed to
accomplish several interactions within a single transaction. 1 scales
horribly, and I haven't attempted 2, but I'd imagine it also scales
badly.

Anyone got anything to add?

Damien