LINQ Skip() Problem

Question

The C# statement below blocks the process and cannot retrieve data if itemToSkip is greater than 0.

 int itemToSkip = 100;
 int itemToTake = 1000;

 var itemList = db.MYTABLEs.Skip(itemToSkip).Take(itemToTake).ToList();

How can I fix it? what is the problem?

Answer 1

Not sure what provider you have that provides db.MYTABLEs. It is really not possible to answer your question unless we know how db.MYTABLEs behaves.

In normal LINQ, skip does not just skip ahead; it has to iterate through the amount of data in order to skip. Therefore, for your 14gb data table, it will be iterating through the first "skip" number of records. If this iteration is slow, you are not saving any cpu/time by skipping.

For some providers, e.g. an SQL source, skip may be implemented using cursors, which can again be slow. If it is SQL Server, it may be optimized with a keyword which may be faster.

If it is LINQ-to-SQL, it translates the query into SQL using a "NOT EXISTS" clause, which will be extremely slow because it has to go through the entire table if the NOT EXISTS clause does not hit an index. See the following (link):

LINQ to SQL translates Skip by using a subquery with the SQL NOT EXISTS clause. This translation has the following limitations:

• The argument must be a set. Multisets are not supported, even if ordered.

• The generated query can be much more complex than the query generated for the base query on which Skip is applied. This complexity can cause decrease in performance or even a time-out.

In other words, the docs says "don't do it."

Only for providers with random-access features, e.g. an in-memory array, will skip be really fast because the provider can just jump ahead.

The worst case will be if you are running on a provider that automatically sorts the entire data set if you use Skip/Take. If you have 14gb of data, then this sort is going to be really slow.

You need to experiment some more to see if your program is hanging on skip, or just hogging all the cpu trying to iterate through.

If you are only trying to divide your data into manageable chunks, you probably should not be using skip/take, which requeries the data source every time.

Answer 2

Skip usually insists on having an explicit sort ordering. Try

var itemList = db.MYTABLEs.OrderBy(r => r.Id).Skip(itemToSkip)

or similar.