Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Data persistency has recently become a really grip for me, especially how long to maintain database connectivity and how many connections are feasible for a given request. I've been using .NET; however, this is a generic question related to any lanaguage. From a .NET perspective, I'm connecting to database using EF4 and for a given website page I might create 6 connection per-request to render a page. The page might consist of news, stock prices, etc, and needs to be constantly updated.

I've recently been looking at unit-of-work (i.e. per request) approach. So basically when a request starts, if required a database connection is opened and destroyed upon end of the request. I'm not convinced it's the best approach yet. I'm looking for opinions and experience on it.

share|improve this question
up vote 1 down vote accepted

My general philosophy has always been that data access layers should be designed to be disconnected and stateless. In other words you have a database layer responsible for fetching different kinds of data and returning it and immediately disconnect the connection (throw it back on the pool)

If you have things like paging it should be implemented on the database level with the sp taking nr entries per page and page to fetch so you don't have to keep anything persistent in memory between requests.

share|improve this answer

Many of the databases out there have a hard limit on number of connections - meaning, if you are to assign x number of connections per request, you will be hitting a ceiling very soon. You should look at connection pooling / caching db objects locally or in cache servers etc. Also db connection Init / destroy is a costly operation. Stick to pooling and let a proxy manage this for you in background.

This might not be a popular opinion, but I am not a big fan of connecting to the databases from the web servers directly either. One way to solve this is by adding additional service layers in between where in you can ensure the db is abstracted out. This way, you don't have to worry about scaling independently / changing db's / partitioning / adding cache layers later.

share|improve this answer
I agree with the abstraction layer. It could be a service layer that managed caching etc, or if warranted, a CQRS style architecture. With a CQRS style you would likely use precomputed "views" of the data you need to serve a request and it wouldn't necessarily need to match the write model. – sbrenton Nov 5 '11 at 18:02

I use a pool of connections. Every time I am done with one, I throw it on a stack. If I need one, I start popping off the stack until I encounter an active connection (connection that didn't timeout while it was in the queue). I use this approach because I have many requests/second and my connections get recycled very fast. They rarely have enough time to timeout inside the stack. Of course you will need to synchronize the Stack. I use Redis as a database, but I assume it works for any type of connection which has an underlying socket.

share|improve this answer
Still you'll be doing a lot of IO actions to the database, which something I've been trying to avoid, unnecessary network traffic. You have pool of 10, then a unit of work might unnecessary try to open ten connections, yes only for second. Problem you might be multiple this across thousands or users. The numbers will eventual add-up to the users waiting for open connection or IO traffic issues. – Nickz May 18 '11 at 7:34

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.