Is connecting to DB still costlier ?

Question

Hi All,

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%5Fpool

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

Answer 3

Connection pooling reduces overheads nowadays.

What I've found is round trips kill performance, generally because of ORMs :-)

Answer 4

DB connectivity is generally going to be slow compared to anything in local RAM. Yes, storing things in RAM causes memory usage to balloon as the number of concurrent users goes up...so? That's what scaling across multiple machines is for. Everything is a tradeoff. You need to look at what you are trying to accomplish and pick what to trade.

Answer 5

To use connection pooling is a good idea. When you have to fetch data from database JSON is a good approach. we should try to avoid Session & View state as these bring down application performance many a times.

Answer 6

I agree with many of the comments already made. Connection pooling means a new database connection does not need to be opened if one is sitting in the pool and available.

Having said that, be careful of repeatedly accessing the database, particularly for master list type data that can be cached. Object-oriented code for database access is notorious for too much database activity, much of which can be prevented with a little planning. Make use of ASP.NET's application cache to store static data that doesn't change often (and when it does, flush and reload the cache).

I'm not a fan of session variables as they are akin to global variables and make for sloppy coding. They also don't fit in with the stateless model of programming adopted by REST, Azure, etc. Just my 2 cents.

Be careful of overloading Viewstate, but if you keep it small, I don't see a problem with it.

-Krip