Linq-to-sql 上下文数据库连接处理
linq-to-sql 数据上下文如何保持数据库连接打开的规则是什么？
当我们对每个更新实体的一个 SubmitChanges() 而不是整批实体的一个 SubmitChanges() 的性能进行一些测试时，问题就出现了。结果：
在一次 SubmitChanges() 调用中插入 3000 个项目...持续时间：1318 毫秒
在一次 SubmitChanges() 调用中插入 3000 个项目 事务范围...持续时间：1280ms
在各个 SubmitChanges() 调用中插入 3000 个项目...持续时间： 4377毫秒
在单个 SubmitChanges() 调用中插入 3000 个项目 交易...持续时间：2901ms
请注意，当为每个更改的实体执行单独的 SubmitChanges() 时，将所有内容放入事务中提高性能，这对我们来说是非常出乎意料的。在 sql server profiler 中，我们可以看到事务中的各个 SubmitChanges() 调用不会为每次调用重置数据库连接，这与没有事务的调用不同。
在什么情况下数据上下文保持连接打开？是否有关于 linq-to-sql 如何处理连接的详细文档？
What are the rules for how a linq-to-sql datacontext keeps the database connection open?
The question came up when we made a few tests on performance for one SubmitChanges() per updated entity instead of one SubmitChanges() for the entire batch of entities. Results:
Inserting 3000 items in one SubmitChanges() call... Duration: 1318ms
Inserting 3000 items in one SubmitChanges() call, within
transactionscope... Duration: 1280msInserting 3000 items in individual SubmitChanges() calls... Duration:
4377msInserting 3000 items in individual SubmitChanges() calls within a
transaction... Duration: 2901ms
Note that when doing individual SubmitChanges() for each changed entity, putting everything within a transaction improves performance, which was quite unexpected to us. In the sql server profiler we can see that the individual SubmitChanges() calls within the transaction do not reset the DB connection for each call, as opposed to the one without the transaction.
In what cases does the data context keep the connection open? Is there any detailed documentation available on how linq-to-sql handles connections?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
你没有展示整个图片；默认情况下，LINQ-to-SQL 将对
SubmitChanges的调用包装在事务中。如果您将其与另一个事务包装在一起，那么您将不会看到连接重置；直到所有SubmitChanges调用完成并且提交外部事务时才能完成。You aren't showing the entire picture; LINQ-to-SQL will wrap a call to
SubmitChangesin a transaction by default. If you are wrapping it with another transaction, then you won't see the connection reset; it can't until all of theSubmitChangescalls are complete and then when the external transaction is committed.除了连接打开/关闭的时间之外，可能还有许多因素会影响计时。
编辑：在意识到 linq2sql 如何分别管理缓存实体和脏实体后，我删除了有关跟踪实体的部分。
通过使用 Reflector 或其他反汇编程序检查 SqlConnectionManager 类上的方法，您可以很好地了解如何在幕后管理连接。如果 SubmitChanges 将提交包装在自己的事务中，则在提交后，SubmitChanges 将在其 IProvider（通常是 SqlProvider，然后使用 SqlConnectionManager）上调用 ClearConnection，但如果 SubmitChanges 是较大事务的一部分，则不会。连接何时打开和关闭取决于是否有其他活动使用 SqlConnectionManager。
There may be a number of factors that could be influencing the timings besides when connections are opened/closed.
edit: I've removed the bit about tracked entities after realizing how linq2sql manages the cached entities and the dirty entities separately.
You can get a good idea how the connections are managed under the covers by using Reflector or some other disassembler to examine the methods on the SqlConnectionManager class. SubmitChanges will call ClearConnection on its IProvider (typically SqlProvider which then uses SqlConnectionManager) after the submit if it wrapped the submit in its own transaction, but not if the SubmitChanges is part of a larger transaction. When the the connection is opened and closed depends on whether there is other activity making use of the SqlConnectionManager.
我最近也被这个搞乱了。调用
SubmitChanges3000 次并不是一个好主意，但是根据插入每条记录的重要性，您可能需要这样做，毕竟只需要 1000 毫秒。事务范围和多个
SubmitChanges是我期望看到的。由于您仍在一个事务中，我希望看到 SQL Server 能够更好地处理这个问题，看起来确实如此。一个SubmitChanges和使用显式/隐式TransactionScope似乎会产生相同的结果，这是可以预料的。那里不应该有任何/太多的性能差异。我认为连接是在需要时创建的，但您必须记住这将在您的提供程序中汇集，因此除非您的连接字符串正在更改，否则您应该连接到相同的连接池，这将产生相同的性能，无论如何的方法。由于 LINQ-SQL 在后台使用
SqlConnection，因此有关它的一些信息如下：http://msdn.microsoft.com/en-us/library/8xx3tyca(VS.80).aspx
如果您使用暴力破解性能方面，请考虑使用显式
TransactionScope转移到存储过程中进行插入。如果这还不够快，请考虑使用SqlBulkCopy。 3000 行插入速度应超过 1000 毫秒。I messed about with this lately also. Calling
SubmitChanges3000 times will not be a good idea, but then depending on how critical it is that each record gets inserted, you may want to do this, after all it only takes 1000ms.The transaction scope and multiple
SubmitChangesis what i'd expect to see. Since your still within one transaction i'd expect to see SQL server handle this better, which it seems to. OneSubmitChangesand using a explicit/implicitTransactionScopeseems to yield the same result, which is to be expected. There shouldn't be any/much of a performance difference there.I think connections are created when needed, but you have to remember this will be pooled within your provider so unless your connection string is changing, you should hook onto the same connection pool which will yield the same performance regardless of approach. Since LINQ-SQL uses
SqlConnectionbehind the scenes, some information about it is at the following:http://msdn.microsoft.com/en-us/library/8xx3tyca(VS.80).aspx
If your after brute force performance, look at moving into a Stored Proceedure for insert with an explicit
TransactionScope. If that isn't fast enough, look at usingSqlBulkCopy. 3000 rows should insert faster than 1000ms.您是否尝试过自己打开和关闭连接：
强制打开 DataContext 的连接 (LINQ)
我认为在这种情况下，您不需要额外的交易。
Have you tried opening and close the connection yourself:
Force the Opening of the DataContext's Connection (LINQ)
I think in that case you do not need the extra transaction.