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

I think OFFSET .. FETCH is very useful when browsing the first pages from your large data (which is happening very often in most applications) and have a performance drawback when querying high order pages from large data.

Check this article for more details regarding performance and alternatives to OFFSET .. FETCH.

Try to apply as many filters to your data before applying paging, so that paging is run against a smaller data volume. It is hard to imagine that the user wants no navigate through 1M rows.