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?

Performance depends on type of database being used and size. You may not be able to speed up results. The driver used to connect to database can also effect speed. What database are you using and which driver? See following for list of drivers : connectionstrings.com — jdweng
– jdweng, Commented Jun 14, 2021 at 7:13
If you are retrieving data from a cache, then why entity-framework tag? Remove the tag and specify in the question that you are running LINQ in-memory query (LINQ to Objects). — Ivan Stoev
– Ivan Stoev, Commented Jun 14, 2021 at 7:31
Please remember that most of us who don't live in India have no idea what is "lakhs of data", at best they can guess it's "lack of data". So please don't use Indian words here. — James Z
– James Z, Commented Jun 14, 2021 at 8:11
If there are 10^5 items in memory, your problem is probably not the posted query. linear, or even n log n complexity, for that many items should be in the order of tens to a hundreds of milliseconds, not seconds. Use a profiler! — JonasH
– JonasH, Commented Jun 14, 2021 at 8:32
Then there is nothing that can be optimized in the shown code. The culprit apparently is in the Data cache implementation. You use terms like "cache", "loading" which are totally unclear (people are assuming cache to be some sort of list, but then there wouldn't be such thing as loading), so you really need to find out what is causing the performance issue and how/if it can be improved. All that inside "cache" implementation, there is nothing we can't help. — Ivan Stoev
– Ivan Stoev, Commented Jun 14, 2021 at 9:38

another.one · Accepted Answer · 2021-06-14 08:16:24Z

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

answered Jun 14, 2021 at 8:16

another.one

511 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related