Opening the database connection once or on every databaseaction?

Question

Im currently creating a webportal with ASP.NET which relies heavily on database usage. Basically, every (well almost every :P ) GET query from any user will result in a query to the database from the webserver.

Now, I'm really new at this, and I'm very concerned about performance. Due to my lack of experience in this area, I don't really know what to expect.

My question is, using ADO.NET, would it be a smarter choice to just leave a static connection open from the webserver to the database, and then check the integrety of this connection serverside before each query to the database? - Or, would I be better off opening the connection before each query and then close it afterwards?

In my head the first option would be the better as you save time handshaking etc. before each query and you save memory both on the database and the server side since you only have one connection, but are there any downfalls to this approach? Could 2 queries send at the same time potentially destroy each others integrity or mix the returned dataset?

I've tried searching everywhere in here and on the web to find some best-practices about this, but with no luck. Closest I got was this: http://stackoverflow.com/questions/312702/is-it-safe-to-keep-database-connections-open-for-long-time , but that seems to be more fitting for distributed systems where you have more than one user of the database, whereas I only got my webserver..

Answer 1

It's better to let ADO.NET handle the connection pooling. It'll persist the connection if it thinks it needs to, but don't use a static connection object. That just smells. It would be better to pass the connection object around to methods that need it, and create the connection in a using block.

Answer 2

You're way early to be worrying about performance.

Anyhow, connections are pooled by the framework. You should be opening them, using them, and disposing of them ASAP.

Something like...

public object Load()
{
  using (SqlConnection cn = new SqlConnection(connectionString))
  using (SqlCommand cm = new SqlCommand(commandString, cn))
  {
    cn.Open();
    return cm.ExecuteScalar();
  }
}

Answer 3

You should always close your connection after finishing your DB interaction. ADO.NET has connection pooling which will take care of efficient connection reuse. Whenever you open 2nd, 3rd and subsequent connections - they'll be taken from a pool with almost no overhead.

Hope this helps.

Answer 4

ADO.NET does connection pooling. When you call close on the connection object it will keep the connection in the pool making the next connection much faster.

Answer 5

You definitely don't want to open a connection for every database call, that will result in extremely poor performance very quickly. Establishing a database connecting is very expensive.

Instead what you should be using is a connection pool. The pool will manage your connections, and try to re-use existing connections when possible.

Answer 6

Your initial hunch is correct. What you need is database connection pooling.

Answer 7

I don't know your platform, but look into Connection Pooling - there must be a library or utility available (either in the base system or as an add-on, or supplied with the database drivers) that will provide the means to pool several active connections to the database, which are ready and raring to be used when you obtain one from the pool.

To be honest, I would expect the pooling to occur by default in any database abstraction library (with an available option to disable it). It appears that ADO.NET does this.

Answer 8

Really the first question to ask is why are you very concerned about performance? What is your expected workload? Have you tried it yet?

But in general, yes, it's smarter to have an open connection that you keep around for a while than to re-open a database connection each time; depending on the kind of connection, network issues, and phase of the moon, it can take a good part of a second or more to make an initial connection; if your workload is such that you expect more than a GET every five seconds or so, you'll be happier with a standing connection.

Answer 9

I'd be thinking more about caching than advanced connection pooling. Every get requires a database hit?

If its a portal you've got common content and user specific content, using the Cache you can store common items as well as with a mangled key (with the users id) you can store user specific items.