它是确定留下一个SQL连接打开？ [英] Is it ok to leave a sql connection open?

问题描述

我是通过一个COM口不断接收数据，并做一些解码。当解码完成我必须存储在SQL数据库的结果。我想，因为解码完成（在while循环始终运行）几十每秒时间和数据需要存储到次每秒数据库打，如果它是明智的打开和关闭到SQL Server的连接每个while循环或让它开放，并继续将数据写入到数据库中。所有的
首先是这可能吗？其次，如果连接保持打开状态下第三方应用程序或计算机访问该数据库在同一时间和我的PROGRAMM存储读取数据的数据？

I am receiving data through a com port continuously and doing some decoding. When decoding is done i have to store the results in a sql database. I am thinking since the decoding is done (in a while loop always running) dozens of times per second and data need to be stored to the database dozen of times every second if it is wise to open and close the connection to the sql server in each while loop or just leave it open and continue to write data to the database. First of all is this possible? Secondly if the connection remains open can third party applications or computer access the database at the same time and read data as my programm stores data?

推荐答案

一个数据库支持多个并发连接，所以是的，它是在这种情况下离开DB连接开放非常可行的 - 你只会锁定别人，如果你有即长时间运行的查询导致行/表锁。只是关闭完成后的连接。

A database supports more than one concurrent connection, so yes it is very feasible in this scenario to leave the DB connection open - you will only lock out others if you have i.e. a long running query that results in row/ table locking. Just close the connection when you are done.

也可以考虑，虽然大部分的DB（即SQL Server）的使用的connection汇集内部，因此，即使您关闭数据库连接，它只是又回到了泳池，不是物理封闭 - 池管理物理DB连接 - 这结果在更好的性能，所以打开/关闭连接急剧减少的影响。

Also consider though that most DBs (i.e. SQL Server) use connection pooling internally, so even though you close a DB connection it just goes back to the pool and is not physically closed - the pool manages the physical DB connections - this results in much better performance, so the impact of opening/closing connections rapidly is reduced.

从的 MSDN ：

连接池减少的次数是新的连接
必须打开。池程序保持对物理
连接的所有权。它管理保活了一套有效
的连接，为每一个给定的连接配置连接。每当用户
调用打开一个连接时，池会在池中的可用
连接。如果一个池的连接可用，
返回给调用者，而不是打开一个新的连接。当
应用程序调用关闭连接上，池返回到
汇集一组活动连接而不是关闭它。一旦
连接返回到池中，它已准备好对
下一开放呼叫到被重复使用。

Connection pooling reduces the number of times that new connections must be opened. The pooler maintains ownership of the physical connection. It manages connections by keeping alive a set of active connections for each given connection configuration. Whenever a user calls Open on a connection, the pooler looks for an available connection in the pool. If a pooled connection is available, it returns it to the caller instead of opening a new connection. When the application calls Close on the connection, the pooler returns it to the pooled set of active connections instead of closing it. Once the connection is returned to the pool, it is ready to be reused on the next Open call.

这篇关于它是确定留下一个SQL连接打开？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！