为什么重用 DataContext 会对性能产生负面影响?

问题描述

经过 公平 金额 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:

1. 如果 DataContext 的内存中标识缓存变得如此之大，以至于它必须开始写入页面文件，那么您的性能将受制于 HD 的读头速度，并且实际上没有理由使用完全缓存.

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.

内存中的标识对象越多，每次保存操作所需的时间就越长.

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 会对性能产生负面影响?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！