141

At the moment I create a database connection when my web page is first loaded. I then process the page and run any queries against that conection. Is this the best way to do it or should I be creating a database connection each time I run a query?

p.s It makes more sense to me to create 1 connection and use it but I don't know if this can cause any other issues.

I am using C# (ASP.NET) with MSSQL.

4 Answers 4

163

If you create one per query / transaction, it is much easier to manage "closing" the connections.

I can see why common sense dictates that you should open one and use it throughout, but you will run into problems with dropped connections and multithreading. So your next step will be to open a pool, say of 50, connections and keep them all open, doling them out to different processes. And then you'll find out that this is exactly what the .NET framework does for you already.

If you open a connection when you need it and dispose of it when you've finished, that will not actually close the connection, it'll just return it to the connection pool to be used again.

7
  • 2
    The webpage you linked to is specific to SQL Server. Does .NET also provide automatic pooling when connecting to other databases eg - Oracle, Sqlite, MySql?
    – briddums
    Mar 29, 2012 at 18:32
  • 1
    @briddums: Depends on the provider assembly. I am certain that both Microsoft's Oracle implementation and Oracle's own both support connection pooling, because I've used them. I've heard that there's a MySql one that does, and I'd expect the providers in Spring.NET to support pooling, but you're better off searching or asking the provider directly than asking me.
    – pdr
    Mar 29, 2012 at 18:50
  • 2
    It should known that opening, running a query, and disposing a connection, even in a loop, is equally as fast, and sometimes FASTER than opening it once and looping the query. Always just dispose. It's more secure, and FAST. Do not worry about the overhead of the getting a connection from the pool--it is so trivial.
    – smdrager
    Oct 2, 2012 at 17:33
  • 1
    @AlexVPerl: I'd need to profile and see evidence that a) there is an actual saving in not returning the connection to the pool and b) that cost isn't a result of hitting the max connection pool (cause, if it did, then any saving would mean a cost elsewhere -- potentially even timeouts waiting for a connection). I'd also need to know that there weren't any other costs, like problems caused by connections dropping between calls. I'd also need to be worried about millisecond-level efficiency savings. An unlikely use-case, but never say never.
    – pdr
    Nov 22, 2015 at 16:12
  • 1
    The MySQL Connector/Net supports connection pooling for better performance and scalability with database-intensive applications. This is enabled by default. Mar 18, 2018 at 18:25
44

Best practice it to create one connection per query - and in the case of displaying data, best practice is to have the query bring in all the needed data in one go.

Background information:

In .NET, calling SqlConnection.Open() will by default always transparently use connection pooling (see "Using Connection Pooling with SQL Server" on MSDN). So you can just grab a new connection using Open(), and call Close() when you're done, and .NET will do the right thing.

Note that without connection pooling, one connection per query would be a very bad idea because creating real database connections can be very costly (authentication, network overhead etc.), and the number of simultaneous open connections is usually very limited.

8
  • 7
    @webnoob - Since .NET uses connection pooling, no, it doesn't. The reason is that connections can get closed, reallocated etc - so reusing a connection is not good practice.
    – Oded
    Mar 29, 2012 at 9:51
  • 12
    -1 The answer is rather misleading. Creating a connection per query is a very bad idea. What you probably mean is "retrieve a new connection for each query from the connection pool" - but that's not the same as creating a connection.
    – sleske
    Mar 29, 2012 at 13:54
  • 1
    @sleske - How is that different from the answer by pdr?
    – Oded
    Mar 29, 2012 at 14:05
  • 3
    @Oded: Ah, I see. In .NET, calling SqlConnection.Open() will always transparently use connection pooling. So the difference between "open a connection" and "retriebve a connection from a pool" does not exist. My misunderstanding. I took the liberty of editing a little explanation into the question, and took back the vote.
    – sleske
    Mar 29, 2012 at 14:28
  • 2
    @eaglei22 - it certainly should do so (see docs.microsoft.com/en-us/dotnet/framework/data/adonet/…). In general, you would want to return the connection to the pool as soon as possible, though, if you are issuing a number of queries in sequence, it might be better to reuse a connection as you suggest. You would need to test and see which approach is better for you (I don't know what criteria you use - check both ways and see the effect on your chosen metrics).
    – Oded
    Jun 27, 2018 at 13:08
6

Developers sometimes want to optimize or simplify their code to re-use their database connection objects. Given the context of this question puts us in the .Net ecosystem, this is almost always a mistake. (Note: many other platforms handle this in similar ways, as well).

ADO.Net has a feature called Connection Pooling. When you create and open a new connection object, what you're really doing is requesting a connection from a pool. When you close a connection, you return it to the pool.

It's important to understand the objects we use directly in code — SqlConnection, MySqlConnection, OleDbConnection, etc — are merely wrappers around a real underlying connection managed by ADO.Net. The ADO.Net real connections are much "heavier" and more expensive from a performance standpoint. It's these underlying objects that have worries like authentication, network transit, encryption, etc, and those things far outweigh the small amount of memory in the object you actually see in your own code.

When you try to re-use your connection object, you gain efficiency in the small thing at the expense of the much bigger thing. It's a net loss, because you break the ability for the ADO.Net connection pool to effectively manage the important underlying connections.

Re-using a connection across an application or http request can also force you to unintentionally serialize operations that might otherwise be able to run asynchronously, and become a performance bottleneck. I've seen this happen in real applications.


In the case of the web page example here, where you at least only keep the small connection for the duration of a single http request/response, you could gain even more efficiency by evaluating what queries you run in your request pipeline, and try getting them down to as few separate requests to the database as possible (hint: you can submit more than one query in a single SQL string, and use DataReader.NextResult() or check different tables in a DataSet to move between them).

In other words, rather than thinking in terms of minimizing the number of connection objects you create (by re-using one connection for an application or http request), think in terms of one connection for each time you call out to the database... each round trip. Then try to minimize the number of connections by minimizing the number of those trips. In this way you can satisfy both goals.


But that's just one kind of optimization. There's also optimizing programmer time, and gaining effective code re-use. Developers don't want to write the same boilerplate code over and over again just to get a connection object that's open and ready-to-use. It's not only tedious, it's a way to introduce bugs into a program.

Even here, though, it's still generally better to have one connection per query (or round trip). There are patterns you can use to help avoid re-writing the same boilerplate code, while still keeping the goal of one connection per call to the database. Here is one example I like, but there are many others.

1
  • I'm late to this party, but I think this answer covers some important points :) Sep 23, 2019 at 21:21
0

Personally, I usually find that software acquires a connection (often, "behind the scenes"), then re-uses that connection until the routine which acquired it exits. I anticipate finding little reason to "improve upon" this.

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