实体框架代码优先/性能问题 [英] Entity framework Code First optimize / performance issues

问题描述

我正在使用Entity框架，Code第一种方法，而数据库方面我们有两个表 - FormEntities和FormValues。

<我们正在从上面两个表中检索值，该表包含大约50个缺陷的条目。因此，在检索时我面临与性能相关的问题。

代码详细信息

var orderedFormEntitiesQueryable = _context.FormEntities

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; .Include（e => e.FormValues）

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; 。包括（e => e.Children）

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; .Include（e => e.Children.Select（c => c.FormValues））

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; .WasCreatedBy（accountId）

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; .IsEntityOfType（returnEntityType）

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; .WhereSearchableFormValuesContains（条件，searchableFields，applType）

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; .Distinct（）

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; .GetByDashboard（）

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; .NotcontainWithdrawn（）

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; .IsOrderByGridFilters（orderByFormValue，isAscendingOrder）;

var orderedFormEntities = orderedFormEntitiesQueryable.ToList（）;

这里 - &wasbsp; WasCreatedBy，IsEntityOfType，WhereSearchableFormValuesContains，GetByDashboard，NotcontainWithdrawn，IsOrderByGridFilters

是返回IQueryable< FormEntity>的扩展方法。

但是上面的查询"orderedFormEntitiesQueryable"需要大约8到10分钟才能执行，任何人都可以告诉我如何在Code第一种方法中进行优化。

问候

Uday Mahajan

解决方案

你好UdayMahajan，

根据您的描述和相关代码，它似乎是一个复杂的查询，执行大约需要8到10分钟。

请尝试使用以下代码获取SQL语句并在数据库中执行SQL语句，检查它是否花费了很多时间。

此外，请尝试使用延迟加载并检查是否存在是比较快的。像这样：

 var orderedFormEntitiesQueryable = _context.FormEntities 
 .WasCreatedBy（accountId）
 .IsEntityOfType（returnEntityType）
 .WhereSearchableFormValuesContains（ criteria，searchableFields，applType）
 .Distinct（）
 .GetByDashboard（）
 .NotcontainWithdrawn（）
 .IsOrderByGridFilters（orderByFormValue，isAscendingOrder）; 

有关EF 4,5和6的性能注意事项的更多信息，请参阅：

https://msdn.microsoft.com/en-us/library/hh949853（v = vs.113 ）.aspx

祝你好运，

Cole Wu

Hi,

I am using Entity framework, Code first approach, and database side we have two tables - FormEntities and FormValues.

We are retrieving values from the above two tables, the table contains entries around 50 lacks.So while retrieving I am facing performance related issues.

Code details

var orderedFormEntitiesQueryable = _context.FormEntities
                .Include(e => e.FormValues)
                .Include(e => e.Children)
                .Include(e => e.Children.Select(c => c.FormValues))
                .WasCreatedBy(accountId)
                .IsEntityOfType(returnEntityType)
                .WhereSearchableFormValuesContains(criteria, searchableFields, applType)
                .Distinct()
                .GetByDashboard()
                .NotcontainWithdrawn()
                .IsOrderByGridFilters(orderByFormValue, isAscendingOrder);

var orderedFormEntities = orderedFormEntitiesQueryable.ToList();

here -  WasCreatedBy,IsEntityOfType,WhereSearchableFormValuesContains,GetByDashboard,NotcontainWithdrawn,IsOrderByGridFilters

are the extension methods which returns IQueryable<FormEntity>.

However the above query "orderedFormEntitiesQueryable" takes around 8 to 10 minutes to execute, Can any one tell me how to optimize in Code first approach.

Regards

Uday Mahajan

解决方案

Hi UdayMahajan,

Based on your description and related code, it seems that it is a complex query, which takes around 8 to 10 minutes to execute.

Please try to use the following code to get SQL statement and execute the SQL statement in your database, check if it spend much times.

In addition, please try to use Lazy Loading and check if it is faster. like this:

var orderedFormEntitiesQueryable = _context.FormEntities
                 .WasCreatedBy(accountId)
                 .IsEntityOfType(returnEntityType)
                 .WhereSearchableFormValuesContains(criteria, searchableFields, applType)
                 .Distinct()
                 .GetByDashboard()
                 .NotcontainWithdrawn()
                 .IsOrderByGridFilters(orderByFormValue, isAscendingOrder);

For more information about Performance Considerations for EF 4, 5, and 6, please refer to:

https://msdn.microsoft.com/en-us/library/hh949853(v=vs.113).aspx

Best regards,

Cole Wu

这篇关于实体框架代码优先/性能问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！