問題描述
經過 公平 金額 of 研究 和一些錯誤，我修改了我的代碼，以便在每次查詢數據庫或插入數據時創建一個新的 DataContext.并且經常查詢數據庫——對于處理的 250k 個事務中的每一個，在插入事務之前查詢數據庫以獲取客戶 ID、部門 ID 和類別.
After a fair amount of research and some errors, I modified my code so that it creates a new DataContext each time the database is queried or data is inserted. And the database is queried frequently - for each of 250k transactions that are processed, the database is queried to obtain a customer id, department id, and category before the transaction is inserted.
所以現在我正在嘗試優化代碼，因為它每秒只處理大約 15 個事務.我刪除了一些無關的查詢并添加了一些索引并使其達到 30/秒.然后我想，即使每個人都說 DataContext 是輕量級的，但每個事務創建一個新的 4 次還是要付出一些代價的，所以我嘗試重用 DataContext.令我驚訝的是，我發現重用上下文會導致性能下降到每秒 10 個事務！
So now I'm trying to optimize the code as it was only processing around 15 transactions a second. I removed some extraneous queries and added some indexes and got it up to 30/sec. I then figured that even though everyone says a DataContext is lightweight, it's got to cost something to create a new one 4 times per transaction, so I tried reusing the DataContext. I found, much to my surprise, that reusing the context caused performance to degrade to 10 transactions a second!
為什么會這樣?是否因為 DataContext 將實體緩存在內存中并在查詢數據庫之前首先搜索其內存列表?因此，例如，如果我正在尋找名稱為MCS"的客戶的客戶 ID(主鍵)，并且客戶名稱列上有一個聚集索引，以便數據庫查詢快速，內存中查找會更慢?
Why would this be the case? Is it because the DataContext caches the entities in memory and first searches through its in-memory list before querying the database? So that if, for example, I'm looking for the customer id (primary key) for the customer with name 'MCS' and the customer name column has a clustered index on it so that the database query is fast, the in-memory lookup will be slower?
創建/處理如此多的數據庫連接真的會減慢速度，還是這只是另一個過早的優化?如果這是真的，有沒有辦法重用 DataContext 但讓它為每個 linq-to-sql 查詢執行實際的數據庫查詢?
And is it true that creating/disposing so many db connections could slow things down, or is this just another premature optimization? And if it is true, is there a way to reuse a DataContext but have it perform an actual database query for each linq-to-sql query?
推薦答案
這就是為什么重新使用 DataContext 不是最佳實踐的原因，來自 MSDN DataContext 文檔:
Here's why re-using a DataContext is not a best practice, from the MSDN DataContext documentation:
DataContext 是所有實體映射到數據庫聯系.它跟蹤您的變化對所有檢索到的實體和維護一個身份緩存"保證檢索到的實體超過一次由使用相同的對象實例.
The DataContext is the source of all entities mapped over a database connection. It tracks changes that you made to all retrieved entities and maintains an "identity cache" that guarantees that entities retrieved more than one time are represented by using the same object instance.
一般來說，一個DataContext實例是旨在持續一個單位工作"但是您的應用程序定義那個詞.數據上下文是重量輕，而且不貴創建.一個典型的 LINQ to SQL應用程序創建數據上下文方法范圍內的實例或作為短期班級的成員表示一組相關的邏輯數據庫操作.
In general, a DataContext instance is designed to last for one "unit of work" however your application defines that term. A DataContext is lightweight and is not expensive to create. A typical LINQ to SQL application creates DataContext instances at method scope or as a member of short-lived classes that represent a logical set of related database operations.
如果您將 DataContext 重新用于大量查詢，您的性能會因以下幾個可能的原因而下降:
If you're re-using a DataContext for a large number of queries, your performance will degrade for a couple of possible reasons:
如果 DataContext 的內存中標識緩存變得如此之大，以至于它必須開始寫入頁面文件，那么您的性能將受制于 HD 的讀頭速度，并且實際上沒有理由使用完全緩存.
If DataContext's in-memory identity cache becomes so large that it has to start writing to the pagefile then your performance will be bound to the HD's read-head speed and effectively there won't be a reason to use a cache at all.
內存中的標識對象越多，每次保存操作所需的時間就越長.
The more identity objects there are in memory, the longer each save operation takes.
本質上，您所做的違反了 DataContext 類的 UoW 原則.
Essentially what you're doing is violating the UoW principle for the DataContext class.
打開數據庫連接確實有一些與之相關的開銷，但長時間保持連接打開(這通常也意味著鎖定表)不如快速打開和關閉它們更可取.
Opening database connections does have some overhead associated with it, but keeping a connection open for a long period of time (which often also means locking a table) is less preferable than opening and closing them quickly.
來自 MSDN 的另一個可能對您有幫助也可能沒有幫助的鏈接:
Another link which may or may not help you from MSDN:
如何:重用 ADO 之間的連接.NET 命令和 DataContext(LINQ to SQL)
這篇關于為什么重用 DataContext 會對性能產生負面影響?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！