The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Database connections management

I am currently working on a 3-layer web application. The business layer is in charge of connectign database and retrieving results. I am thinking about what is the best strategy to arrange database connections. There are two methods:
1. Only establish connections when necessary and release the connection right after finishing retrieving the data.
2. Keep the connections as long as possible and share the connections among operations.

From the application scalability perspective, which one is better?
Burner
Tuesday, February 21, 2006
 
 
If you want to load balance between database then you shouldn't use nailed up connections because they can't be redirected.

Otherwise keeping connections up is much faster. Just remember to increase the number connections your database can accept to handle the increased load.
son of parnas
Tuesday, February 21, 2006
 
 
The answer depends almost entirely on what database you're using.
Matt.
Tuesday, February 21, 2006
 
 
You didn't tell us what language/RDBMS you are using. For example, .NET already provides connection pooling so that you don't have to explicitly manage it yourself. In .NET, the prefered method is to connect, read the data, and then disconnect immediately so that the connection is returned to the connection pool.

If we knew what language you were using it might help us to steer you in the right direction. But unless I'm missing something, it sounds like both of the ideas you presented equate to some sort of connection pooling strategy.
Turtle Rustler
Tuesday, February 21, 2006
 
 
I am going to assume those closes are virtual so that the connection pool can disconnect your code as a client. Opening a connection everytime, litteraly, would make very little sense.
son of parnas
Tuesday, February 21, 2006
 
 
It depends on requirements to your application.
If many users connects simultaneously, then you can not create a connection for each of them since a creation of connection is quite expensive task. It takes some time and CPU resources to create a connection. Also a database engine like Oracle RDBMS maintains an amount of memory for each user connections, "cursors".
From my experience, I would say if there are more then a few users connects per second or there are more then 50 users works simultaneosly, then you must share a connection pool between users.
Andrei Lopatenko Send private email
Tuesday, February 21, 2006
 
 
Does Oracle charge by connection? I remember that beeing an issue for the number of connections too.

In MySql we regularly have 150 connections so as long as your clients are scaled appropriately your pool doesn't need to open and close.
son of parnas
Tuesday, February 21, 2006
 
 
Perhaps the only case when you do not need to use connection pooling is the case when each user connects with different database login which is very untypical for 3-layer implementations (never occured in my practice).
so, you have to decide which layer is reesponsible for data security (like access to HR data, payroll, confidential information).
If the database is responsible, then each user connects with differen database user name. In case of 3-tier application, 2nd layer assign a user name to the connection.
If the 2nd layer is responsible for the security, then all users connects with the same database username (of course, 2nd layer user names are different) and 2nd layer logic decides which rights user has.

so, the strategy is implement a connection pooling if you do not implement a specific security policy at the database level. and each user connect with own database login (through the 2nd layer)
Andrei Lopatenko Send private email
Tuesday, February 21, 2006
 
 
"Does Oracle charge by connection? I remember that beeing an issue for the number of connections too."

At one time, they did not. BUT they would charge for the number of processors utilized by the database. I vaguely remember a scenario where a certain number of connections (or more) would drive up the load and the second processor would kick in. So the number of connections did have an indirect influence on the multi-processor/cluster versions of Oracle's databases.

I don't know what the pricing scheme is now in this regard.
TheDavid
Tuesday, February 21, 2006
 
 
in case if you do not rune Oracle Server in the shared servermode then each connection require separate process and the amount of memory on the server machine. so, in that case definitly the middle level must run conenction pulling.
Andrei Lopatenko Send private email
Tuesday, February 21, 2006
 
 
Andrei-
Even if they use separate login DBs, pooling can make sense.  You set up a small separate login pool & allow it to go to zero active connections after a fairly long timeout.  Except for high-activity times like early morning & after lunch when the pool would be fully active, there's almost no resource use.  I've done a few times now for both client-server and networked apps & it provides our clients' IT folks a smidgen more peace-of-mind.
a former big-fiver Send private email
Tuesday, February 21, 2006
 
 
a former big-fiver Send private email

Agree
Andrei Lopatenko Send private email
Tuesday, February 21, 2006
 
 
a former big-fiver

Agree

sorry "Send private email" appeared when copied & pasted your name and email symbol
Andrei Lopatenko Send private email
Tuesday, February 21, 2006
 
 
Oracle pricing is now baed on named user or CPUs -- in a three-tier application you'd license by CPU.

just to clarify, the Oracle server does not run in shared server mode or dedicated server mode -- you can configure the server to allow shared connections, but even then dedicated connections are still possible at the same time. It just affects what options you have in connecting.
David Aldridge Send private email
Wednesday, February 22, 2006
 
 
David,
yes agree, I meant Oracle behaviour determined by "SHARED_SERVERS" parameter
Andrei Lopatenko Send private email
Thursday, February 23, 2006
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz