实体框架跳过方法运行速度非常慢 [英] Entity Framework Skip method running very slow

问题描述

我用我的数据访问层对实体框架5，ObjectContext的和波苏斯。我有一个通用的程序存储库实现和我有使用跳过（分页查询数据库的方法）和Take（）。一切正常，只是跳过了很多行（我说的是170K行）

I'm using Entity Framework 5, ObjectContext and POCOs on my data access layer. I have a generic respository implementation and I have a method that queries the database with paging using Skip() and Take(). Everything works fine, except that the query performance is very slow when skipping a lot of rows (I'm talking about 170k rows)

这是我的查询上摘录当查询性能非常缓慢LINQ到实体：

This is an excerpt of my query on Linq to Entities:

C＃代码：

ObjectContext oc = TheOBJEntitiesFactory.CreateOBJEntitiesContext(connection);
var idPred = oc.CreateObjectSet<view_Trans>("view_Trans").AsQueryable();
idPred = idPred.OrderBy(sortColumn, sortDirection.ToLower().Equals("desc"));
var result = idPred.Skip(iDisplayStart).Take(iDisplayLength);
return new PagedResult<view_Trans>(result, totalRecords);

在翻译查询的Transact-SQL我注意到，而不是使用的 ROW_NUMBER（） 与视图直接条款使得其一个子查询和应用的 ROW_NUMBER（）以子查询的结果...

In the translated query to Transact-SQL I noticed that instead of using the ROW_NUMBER() clause with the view directly its making a sub-query and applying the ROW_NUMBER() to the results of the sub-query...

例如：

select top(10) extent1.A, extent1.B.extent1.C from (
select extent1.A, extent1.B, extent1.C, 
row_number() OVER (ORDER BY [Extent1].[A] DESC) AS [row_number] 
from (
select A,B,C from table as extent1)) as extent1
WHERE [Extent1].[row_number] > 176610
ORDER BY [Extent1].[A] DESC

这需要大约165秒完成。关于如何提高翻译后的查询语句的性能有任何想法？

This takes about 165 seconds to complete. Any idea on how to improve the performance of the translated query statement?

推荐答案

对于那些不按照上述意见，我怀疑问题不在于额外的 SELECT ，因为这额外 SELECT 是出现在不采取165s很多很多EF查询跑步。我终于发现，他的对象集引用的查看，想知道这可能是问题的一部分。一些实验后，他将问题范围缩小到一个 LEFT JOIN 视图中。我建议他跑了该查询的数据库引擎优化顾问;他做到了，这两个指数建议解决了这一问题。

For those not following the comments above, I suspected the problem was not the extra SELECT, since that extra SELECT is present on many, many EF queries which do not take 165s to run. I eventually noticed that his ObjectSet referenced a VIEW and wondered if that might be part of the problem. After some experimentation, he narrowed the problem down to a LEFT JOIN inside the view. I suggested that he ran the Database Tuning Advisor on that query; he did, and the two indices suggested fixed the problem.

这篇关于实体框架跳过方法运行速度非常慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！