sql - Entity Framework Skip/Take is very slow when number to skip is big

Question

So, code is very simple:

var result = dbContext.Skip(x).Take(y).ToList();

When x is big (~1.000.000), query is very slow. y is small - 10, 20.

SQL code for this is: (from sql profiler)

SELECT ...
FROM ...
ORDER BY ...
OFFSET x ROWS FETCH NEXT y ROWS ONLY

The question is if anybody knows how to speed up such paging?

Answer 1

You are right on that, Skip().Take() approach is slow on SQL server. When I noticed that I used another approach and it worked good. Instead of using Linq Skip().Take() - which writes the code you showed - , I explicitly write the SQL as:

select top NTake ... from ... order by ... where orderedByValue > lastRetrievedValue 

this one works fast (considering I have index on the ordered by column(s)).