Creating database connections - Do it once or for each query?
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.
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.
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 - I think that depends on the connector. .Net, for example, doesn't provide a MySql connector. It's written and maintained by MySql. And whilst it works, in my experience the earlier implementation were far from bug free.
@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.
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.
What about the cost of returning the connection to the pool itself. If you do it frequently enough that could outweigh the benefits. So in some use cases if queries are executed in a loop let say it could be faster to re-use 1 connection rather than returning and re-requesting it again and again from the connection pool.
@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.