Linq-to-sql context database connection handling

What are the rule开发者_如何学JAVAs 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: 1280ms

Inserting 3000 items in individual SubmitChanges() calls... Duration: 4377ms

Inserting 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?

---

You aren't showing the entire picture; LINQ-to-SQL will wrap a call to SubmitChanges in 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 the SubmitChanges calls are complete and then when the external transaction is committed.

---

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.

---

I messed about with this lately also. Calling SubmitChanges 3000 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 SubmitChanges is 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. One SubmitChanges and using a explicit/implicit TransactionScope seems 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 SqlConnection behind 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 using SqlBulkCopy. 3000 rows should insert faster than 1000ms.

---

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.