what is the optimal database connection strategy

Question

i have a asp.net mvc website which runs a number of queries for each page. Should i open up a single connection or open and close a connection on each query?

Answer 1

If you are fairly sure that the transactions will finish quickly then use a single connection.

Be sure to check all return results and wrap everything in exception handlingwhere possible.

Answer 2

To avoid unnecessary overhead it's better to use a single connection. But be sure to run the queries in a "try" block and close the connections in a "finally" block to be sure not to leave connections hanging.

try-finally

Answer 3

unitofwork?? this is a great strategy to employ. nhibernate and many others use this pattern.

give it a google for specific details relevant to your needs..

jim

Answer 4

It really doesn't matter. When you use ADO.NET (which includes Linq to SQL, NHibernate and any of the other ORMs), the library employs connection pooling. You can "close" and "reopen" a logical connection a dozen times but the same physical connection will remain open the whole time. So don't concern yourself too much with whether or not the connection is open or closed.

Instead, you should be trying to limit the number of queries you have to run per page, because every round-trip incurs a significant overhead. If you're displaying the same data on every page, cache the results, and set up a cache dependency or expiration if it changes infrequently. Also try to re-use query data by using appropriate joins and/or eager loading (if you're using an ORM that lazy-loads).

Even if the data will always be completely different on every page load, you'll get better performance by using a single stored procedure that returns multiple result sets, than you would by running each query separately.

Bottom line: Forget about the connection strategy and start worrying about the query strategy. Any more than 3-5 queries per page and you're liable to run into serious scale issues.

Answer 5

If you are running multiple queries on a page in regular ADO.NET, then they are run in sequence and connection pooling is going to mean it doesn't matter. Best practice is to open connections on demand and close them immediately - even for multiple queries in the same page. Connection pooling makes this fairly efficient.

When you are using multiple queries, your performance could improve significantly by opening multiple connections simultaneously and use asynchronous ADO, to ensure that all the requests are running at the same time in multiple threads. In this case, you need a connection for each query. But the overall connection time will be reduced.

There is also the potential to use MARS on a single connection, but I'm not a big proponent of that, and it's a lot more limited in functionality.