我用我的数据访问层对实体框架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.
更多推荐
实体框架跳过方法运行速度非常慢
发布评论