Entity Framework Skip method running very slow

Question

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)

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

C# Code:

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);

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...

example:

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

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

Answer 1

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.

Answer 2

One reason for the slowness is probably that your sql is ordering your rows twice.

To control the query, the only option I know of is to call idPred.SqlQuery("Select ...", params). This will allow you to write your own optimized query for the data request.