Why is this LINQ IQueryable Performance for Pagination so poor?

Question

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.

Answer 1

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.