Why is this LINQ IQueryable Performance for Pagination so poor?

c#, iqueryable, linq, performance

Solution

`IQueryable` is irrelevant at that point. The performance issue you're seeing is that each chain returns a new iterator.

So, this:

IDevices
    .Where(i => i.StatusID == 2) // iterates over 1000 items
    .OrderBy(j => j.LastCommunicated) // orders however many items have a StatusID of 2
    .Skip(recordsToSkip) // iterates "recordsToSkip" number of times
    .Take(pageSize) // iterates "pageSize" times

Is a step by step process. First, `Where` executes. This iterates over the entire list. Then `OrderBy` executes, which as you can imagine is quite intensive. `Skip` then iterates through until it finds the index you want to be at. `Take` then iterates over n items before you form a new list.

That being said.. had you just run this query straight against the database, it would have been much quicker.

Problem

``` List<Device> deviceList = GetDevices(); // these are loaded in memory already as a list IQueryable<Device> IDevices = deviceList.AsQueryable<Device>(); deviceList = IDevices.Where(i => i.StatusID == 2).OrderBy(j => j.LastCommunicated).Skip(recordsToSkip).Take(pageSize).ToList(); ``` Given the above code, I have about 1000 devices in the list being queried on. The 3rd statement is very slow. Shouldn't it be fast if it is loaded into memory already? I understand in most scenarios you would apply the whole query to the database call, but in my case I already have my devices loaded in memory. The 1st and 2nd calls are very quick.

Original source