Is connecting to DB still costlier?

Question

I'm relatively newbie to ASP.NET, and just building my second realtime webapplication.

When I was learning ASP.NET a year back, I have been told "It is costlier to connect and grab data from database, so make as much less connection as possible and re-use the retrieved data effectively". The reasons given are

1. A connection to the DB server first needs to be established.
2. The concurrent connections on DB server will be less compared to IIS.
3. Use disconnected architecture so that connection can be closed soon (ofcourse other flexibilites are also there) etc, etc.

And when my study expanded, I learnt other measures of storing data once retrieved from DB, across postbacks. But now, I'm learning one by one that they are costlier than connecting to DB. like...

1. Session Variables : Memory size bubbles with number of users
2. View State : The process of encryption and travel makes it costlier.
3. Application Variables : Not so useful in all circumstances.
4. Cache : (I know them, but not used them till now).

Also Oflate, I'm repeatedly advaised not to store retrieved tables in session, view state etc., but to connect to DB and grab it afresh on each postback.

And viewing this in light of SQL Server based Session State management, where the Framework itself used DB to store session. I feel, I should unlearn my initial learning and perception about database connectivity.

What do you say ???

Any other suggestions / tips are also welcome.

Thanks

Answer 1

Generally what you're stating is true, but connection pooling takes away a lot of the overhead of making connections.

When using connection pooling, you have a collection of connections which are used when requests are made to a database. Those connections are then recycled for subsequent uses. I'm doing a very poor job of explaining it, but as always wikipedia has some good information to get you started.

http://en.wikipedia.org/wiki/Connection_pool

If you're looking for a silver bullet, such as always use xxx, unfortunately there isn't one. You'll need to evaluate each scenario and make a determination there.

For example, if you've got a slow connection to your database server, you're probably going to want to cache the data your retrieve to minimize the number of calls you make to the database.

On the other hand, if you've got a system with limited resources, calling to the database frequently could be a valid option.

You'll have to evaluate these situations for every system you develop to get the most out of your software, but if architected properly it is usually fairly easy to adapt the system to any constraints that you run into.

Answer 2

Connection pooling can greatly limit the cost of establishing connections to the database. Connection pooling is an ADO.NET (I think) technology that will essentially resue a database connection as long as the connection string is the same. I would also be careful about caching data, unless you know that your cached data is fairly static.

So, in summary, I would say not to worry about the cost of establishing connections to the database. Most N-Tier, disconnected, applications make frequent connections to the database. I don't think you have to worry about this.

Randy