最佳的数据库连接策略是什么
我有一个 ASP.NET MVC 网站，它为每个页面运行多个查询。我应该打开一个连接还是在每个查询上打开和关闭一个连接？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
这真的没关系。当您使用 ADO.NET（包括 Linq to SQL、NHibernate 和任何其他 ORM）时，该库会使用连接池。您可以“关闭”和“重新打开”逻辑连接十几次，但同一个物理连接将始终保持打开状态。因此，不要太关心连接是打开还是关闭。
相反，您应该尝试限制每页必须运行的查询数量，因为每次往返都会产生大量开销。如果您在每个页面上显示相同的数据，请缓存结果，并设置缓存依赖项或过期（如果它不经常更改）。还可以尝试通过使用适当的联接和/或预先加载来重用查询数据（如果您使用的是延迟加载的 ORM）。
即使每次页面加载时数据总是完全不同，与单独运行每个查询相比，使用返回多个结果集的单个存储过程也会获得更好的性能。
底线：忘记连接策略并开始担心查询策略。每页查询超过 3-5 个，您可能会遇到严重的规模问题。
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.
如果您在常规 ADO.NET 的一个页面上运行多个查询，那么它们将按顺序运行，并且连接池意味着这并不重要。最佳实践是按需打开连接并立即关闭它们 - 即使对于同一页面中的多个查询也是如此。连接池使这变得相当高效。
当您使用多个查询时，通过同时打开多个连接并使用异步 ADO，确保所有请求同时在多个线程中运行，您的性能可以显着提高。在这种情况下，每个查询都需要一个连接。但整体连接时间会减少。
还有在单个连接上使用 MARS 的潜力，但我不太支持这种做法，而且它的功能受到更多限制。
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.
如果您相当确定事务将很快完成，则使用单个连接。
请务必检查所有返回结果，并尽可能将所有内容包装在异常处理中。
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.
为了避免不必要的开销，最好使用单个连接。但请务必在“try”块中运行查询并在“finally”块中关闭连接，以确保不会使连接挂起。
最后尝试
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
工作单位？？这是一个很好的策略。 nhibernate 和许多其他人都使用这种模式。
谷歌一下，了解与您的需求相关的具体细节。
吉姆
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