EntityFramework：在〜11.000个记录上调用IQueryable上的ToList（）需要10秒 [英] EntityFramework : Calling ToList() on IQueryable with ~11.000 records takes 10 seconds

问题描述

我想从SQL Express 2008 R2服务器返回相对较多的记录，通过EntityFramework 4通过WCF服务返回给WCF客户端。我的测试表目前包含大约11.000条记录。 LINQ查询是这样简单的：

I want to return a relatively large number of records from SQL Express 2008 R2 server, via EntityFramework 4 through WCF service to a WCF client. My test table contains around 11.000 records at the moment. The LINQ query is as simple as this:

Database DB = new Database(); // create object context
var retValue = DB.Entities.Persons
        .Include("District")
        .Include("District.City")
        .Include("District.City.State")
        .Include("Nationality")

return retValue.ToList();

这需要大约10秒钟才能完成。

This takes about 10 seconds to complete.

在SQL Server Managament Studio中执行时，相同的SELECT查询需要不到1秒的时间。

The same SELECT query takes less than 1 second when executed in SQL Server Managament Studio.

在EF中是否需要缓慢？

Does it have to be that slow in EF ?

推荐答案

您的查询不简单，它包含很多连接（由于 Include s ），更重要的是它可能会返回大量重复的数据，特别是如果包含的导航属性是集合： https://stackoverflow.com/ a / 5522195/270591

Your query is not simple, it contains a lot of joins (due to the Includes) and more importantly it might return a lot of duplicated data, especially if the included navigation properties are collections: https://stackoverflow.com/a/5522195/270591

当数据库的结果返回到实体框架时，时间消耗部分是对象实现，并将实体附加到上下文上下文

The time comsuming part is object materialization and attaching the entities to the context when the result from the database is returned to the Entity Framework context.

这是通过您的测量（在您的问题的评论中）证实，在同一上下文中的第二个查询非常快。在这种情况下，EF将对数据库执行查询，但不需要再次实现对象，因为它们仍然附加到上下文中。

This is confirmed by your measurements (in the comments to your question) that a second query within the same context is very fast. In this case EF will perform a query to the database but doesn't need to materialize the objects again because they are still attached to the context.

如果运行第二个在第二个上下文中查询生成的实体必须附加到新的上下文中 - 而且此步骤再次缓慢（也可以通过测量确认）。

If you run the second query in a second context the resulting entities must the attached to the new context - and this step is again slow (also confirmed by your measurements).

这可能是一个点其中与EF 的查询实际上较慢，并且与原始SQL查询相比增加了大量开销。 EF需要创建许多数据结构，以便在上下文中更改跟踪和管理对象标识，从而消耗额外的时间。

This is probably a point where a query with EF is in fact slow and adds a lot of overhead compared to a raw SQL query. EF needs to create many data structures prepared for change tracking and managing object identities in the context which consumes additional time.

我可以看到提高性能的唯一方法是禁用改变跟踪（假设你不需要它的操作）。在EF 4.0 / ObjectContext 中将是：

The only way I can see to improve the performance is disabling change tracking (supposed, you don't need it for your operations). In EF 4.0 / ObjectContext it would be:

Database DB = new Database();
DB.Entities.Persons.MergeOption = MergeOption.NoTracking;
// MergeOption is in System.Data.Objects namespace

使用这种方法时，必须知道，即使相关对象具有相同的键，相关对象也将被创建为单独的对象 - 启用的更改跟踪不是这样，因为附加到上下文将避免重复。

When using this approach, one has to be aware though that related objects will be created as separate objects even when they have the same key - which is not the case with enabled change tracking because attaching to the context will avoid this duplication.

所以，潜在的更多的对象将被加载到内存中。如果这是适得其反，并且实际上降低了性能，或者如果它仍然表现更好，则是一个测试的问题。

So, potentially more objects will be loaded into memory. If this is counterproductive and degrades actually the performance even more or if it still performs better is a matter of a test.

这篇关于EntityFramework：在〜11.000个记录上调用IQueryable上的ToList（）需要10秒的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！