为什么会重用一个DataContext有负面影响性能？ [英] Why would reusing a DataContext have a negative performance impact?

问题描述

后 量的的>的研究一些的错误，我修改了代码，以便它创建一个新的DataContext的每个数据库查询或数据被插入的时间。而数据库是经常查询 - 为每个被处理25万的交易，查询数据库获取客户ID，部门ID和类别插在交易之前

所以，现在我想对代码进行优化，因为它是只处理约15成交第二。我删除了一些无关的查询，并增加了一些指标，得到它高达30 /秒。后来我想通了，即使大家说一个DataContext是轻量级的，它有成本的东西创造每笔交易的一个新的4倍，所以我试图重新使用的DataContext。我发现，令我吃惊的是，重用的背景下造成的性能降低到10交易一秒钟！

为什么会变成这样？难道是因为在DataContext通过其在内存中的列表查询数据库之前缓存在内存中，并首先搜索的实体？所以，如果，例如，我在寻找客户ID（主键）与名称'MCS'顾客和客户的名字列上有一个聚集索引，使数据库查询速度快，内存查找速度会变慢？

和这是真的，创建/配置这么多的DB连接可能会慢下来，或者这只是一个不成熟的优化？如果这是真的，有没有办法重用一个DataContext但它为每个LINQ到SQL查询？

解决方案<执行实际的数据库查询/ DIV>

这也是为什么重复使用一个DataContext是不是最好的做法，从的 MSDN文档DataContext的：

DataContext的是映射在一个数据库$所有
实体的源泉b $ b连接。 它会跟踪你
到所有检索到的实体和
维持一个身份缓存中所做的更改是
保证实体检索
以上的时间由$ b表示$ b使用相同的对象实例。

在一般情况下，一个DataContext实例是
设计寿命为
中的一个单位工作，然而你的应用程序定义
这个词。一个DataContext是
重量轻，不昂贵创建于
。一个典型的LINQ to SQL
应用在方法范围或作为短命类的
会员的
表示一组逻辑相关的
数据库操作创建的DataContext
实例

如果您正在重新使用一个DataContext了大量的查询，你的表现会降低了几个可能的原因

1. 如果DataContext中的内存中的身份缓存变得如此之大，它已经开始写入页面文件那么你的表现会绑定到高清的读取头的速度和有效的，不会有什么理由用一个缓存的。




2. 越是标识对象内存中有，较长每次保存操作需要。

实际上你在做什么违反了UOW原则DataContext类

打开数据库连接确实有与它相关联的一些开销，但保持一个连接打开的很长一段时间（通常也意味着锁定的表）是较少优选比打开并迅速关闭它们。

另一个链接，可能会或可能不会帮助你从MSDN：

的How到：重用ADO.NET命令和一个DataContext之间的连接（LINQ到SQL）

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.

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!

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?

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?

解决方案

Here's why re-using a DataContext is not a best practice, from the MSDN DataContext documentation:

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.

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.

If you're re-using a DataContext for a large number of queries, your performance will degrade for a couple of possible reasons:

1. 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.

2. The more identity objects there are in memory, the longer each save operation takes.

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.

Another link which may or may not help you from MSDN:

How to: Reuse a Connection Between an ADO.NET Command and a DataContext (LINQ to SQL)

这篇关于为什么会重用一个DataContext有负面影响性能？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！