Optimize the slow query in-memory List using LINQ for retrieving data

Question

I have a LINQ query that I am having trouble optimizing. It takes about 6 seconds to run.

IList<Note> notes;
data = client.Configuration.Data.OfType<UWData>()
    .Where(s => s.ShouldDisplay)
    .OrderByDescending(s => s.Id)
    .ToList()
    .Select(n => (Note)n)
    .ToList();

client.Configuration.Data contains hundreds of thousands of data (100,000 items). client.Configuration.Data is a cache, and I am retrieving data from the cache. client.Configuration.Data contains 100,000 items, and we have 10 items which is the type of UWData. I want to select the 10 UWData.

var data = client.Configuration.Data.OfType<UWData>().ToList();
data = UWData
    .Where(s => s.ShouldDisplay)
    .OrderByDescending(s => s.Id)
    .ToList()
    .Select(n => (Note)n)
    .ToList();
            

The above code also taking a similar time to load. Sometimes it takes around 4 seconds to load (exactly 3,356 ms). Any ideas on how I could speed this up?

Answer 1

Whenever you are using some sort of ORM remember to keep an eye out for SQL joins (you could extract the generated SQL of your ORM) also as best practice select only necessary columns and use indexes