从LINQ升级到SQL时，如何最大限度地降低EF 4.1的性能？ [英] How do you minimize the performance hit when upgrading to EF 4.1 from LINQ to SQL?

问题描述

我最近更新了一个应用程序，LINQ to SQL和SQL Server CE 3.5到实体框架4.1代码优先和SQL Server CE 4.0，现在运行速度明显较慢。我做了一些以前的秒表测试，并且我的应用程序的大多数主要操作似乎运行平均慢40％。

我正在使用所有的默认策略以及EF Code First的配置，除了禁用级联删除。

当我最初发布这个问题时，我专注于一个似乎要花费很长时间的查询，但是自从意识到这是第一次运行的速度非常慢（见下面的评论线）。

我现在看到的是，大多数查询正在运行慢 - 不是很慢，但慢到足以迅速加起来，因为应用程序执行的大多数操作涉及多个查询。

此应用程序具有非常小的数据库。 SQL CE（.sdf）文件只有458 KB，最大的表格不到250条记录。

下面是一个示例POCO类：

  public class Target 
 {
 public int Id {get;组; } 
 public int TrialDefinitionId {get;组; } 
 public int Number {get;组; } 
 public int X {get;组; } 
 public int Y {get;组; } 
 public string Phase {get;组; } 
 public virtual TrialDefinition TrialDefinition {get;组; } 
} 
  

我所有的类遵循这个基本模式（简单类型+虚拟属性获取通过外键链接的对象）。我有一个课程使用 ICollection 来获得多对一关系的列表。

Final注意：我正在使用一个存储库模式作为调解器，并且使用块将一个存储库的每个用法都放在一个中。对于获取操作，一旦我从数据库中获取了我需要的数据，就会导致实体变得脱离。

有没有人有任何具体的策略提高我的EF Code First应用程序的性能？请记住，我没有机会详细阅读EF。我大部分只是想尽可能快地毫不费力地从LINQ迁移到SQL到EF。对我来说最有用的答案是一个包含改变具体策略或配置或其他设置的答案。

解决方案

最后注意事项：我正在使用一个存储库模式作为一个调解器，并且存储库的每个用法都被放置在一个使用块中。对于获取操作，一旦我从数据库中获取了我需要的数据，就会导致实体变得分离。

不需要...

1. 实体框架的默认架构已经实现了存储库模式。


2. 保持ObjectContext活着并不意味着你保持与数据库的连接。


3. 只有当您从数据库加载或保存更改时，才会抓取连接池的新连接并执行操作。 / li>
   

当然，使用块会减慢，因为每个使用块都会执行以下操作，

<

初始化上下文（需要从资源中加载元数据）

验证几件事

打开与DB的连接

执行你的任务

清理并关闭DB

现在头两步一定会花费大量的时间，你会有多少相同类型的对象在您的应用程序中生活更长时间，因为每个新的上下文将为每个查询创建相同对象的新副本。

实体框架已经实现了身份映射，这意味着它将在上下文的整个生命周期中保持对象活着，并且只有一个对象的副本，这不仅可以节省内存，而且还会执行得更快。

我建议不要对每个查询或更小的步骤使用使用块，而应该在您的应用程序的整个生命周期内保持您的ObjectContext。而您根本不需要实现缓存或存储库。

I recently updated an app with LINQ to SQL and SQL Server CE 3.5 to Entity Framework 4.1 Code First and SQL Server CE 4.0, and it's now running noticeably slower. I did some before vs. after stopwatch testing, and most major operations of my app appear to be running about 40% slower on average.

I'm using all default strategies and configurations for EF Code First except for disabling cascading deletes.

When I originally posted this question, I was focused on one query that seemed to be taking particularly long, but I've since realized that it was only particularly slow on first run (see the comment thread below).

What I now think I'm seeing is that most queries are running slower--not dramatically slower, but slow enough to quickly add up as most operations the app performs involve several queries.

This app has a very small database. The SQL CE (.sdf) file is only 458 KB, and the largest table has less than 250 records.

Here's an example POCO class:

public class Target
{
    public int Id { get; set; }
    public int TrialDefinitionId { get; set; }
    public int Number { get; set; }
    public int X { get; set; }
    public int Y { get; set; }
    public string Phase { get; set; }
    public virtual TrialDefinition TrialDefinition { get; set; }
}

All my classes follow this basic pattern (simple types + virtual properties to obtain objects linked by foreign keys). I have one class that uses an ICollection to obtain a listing for a many-to-one relationship.

Final note: I'm using a repository pattern as a mediator, and each usage of a repository is placed in a using block. For "get" operations, this results in entities becoming detached once I've obtained the data I need from the database.

Does anyone have any specific strategies for improving the performance of my EF Code First app? Please keep in mind that I haven't had a chance yet to read up on EF in much detail. I'm mostly just trying to migrate as quickly and painlessly as possible from LINQ to SQL to EF. The most useful answer for me would be one that consists of changing specific strategies or configurations or other settings.

解决方案

Final note: I'm using a repository pattern as a mediator, and each usage of a repository is placed in a using block. For "get" operations, this results in entities becoming detached once I've obtained the data I need from the database.

Well this is not required...

1. Entity Framework's default architecture already implements repository pattern.
2. Keeping ObjectContext alive does not mean you are keeping your connection to database alive.
3. Only when you are loading from or saving changes to database, a new connection from connection pool is grabbed and operation is performed.

Ofcourse, using block will slow down because each using block will do following,

1. Initialize Context (requires loading metadata from resources)
2. Validate few things
3. Open connection to DB
4. Perform your tasks
5. Clean up and close DB

Now first two steps sure will take lot of time, and you will have multiple objects of same type living longer in your app because each new context will create a new copy of same object for every query.

Entity Framework already implements Identity Map, that means that it will keep the object alive and only one copy of object for same primary key throughout the lifetime of context, that will not only save memory but will also perform faster.

I would advise to not use Using blocks for every query or smaller steps but rather, you should keep your ObjectContext alive throughout lifetime of your application. And you do not need to implement caching or repository at all.

这篇关于从LINQ升级到SQL时，如何最大限度地降低EF 4.1的性能？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！