LINQ performance

Question

I am reading records from database and check some conditions and store in List<Result>. Result is a class. Then performing LINQ query in List<Result> like grouping, counting etc. So there may be chance that min 50,000 records in List<Result>, so in this whether its better to go for LINQ (or) reinsert the records to db and perform the queries?

Answer 1

I'm currently asking the same kind of thing right now. I don't really know the exact answer either, but from what I know, LINQ is not well know to be fast on objects. Also, since List is not indexed, when you do advance query on them, the backend will probably need to do a lot of computing to get what you asked for. Also, this code is generic, so it means slower execution.

The best thing would be, if you are able, do everything in one query, or even do a startproc to do your processing. Or another possibility, if you are always checking the same initial condition, create a view and do your query directly on this table (instead of reinserting from the client). I think that if you have more than 50,000 results, probably using a list is not a good idea (Memory and Performance).

It probably doesn't answer your question directly, but other than doing benchmark, you won't know. It really depends on what you are doing with the data.

Answer 2

Why not store it in an IQueryable instead of a List and using LINQ to SQL or LINQ to Entities, the actual dataset will never be pulled into memory, and the queries will actually go down to the database to run.

Example:

Database db = new Database(); // this is what L2E gives you...

var children = db.Person.Where(p => p.Age < 21); // no actual database query performed


// will do : "select count(*) from Person where Age < 21"
int numChildren = children.Count();

var grouped = children.GroupBy(p => p.Age); // no actual query

int youngest = children.Min(p => p.Age); // performs query

int numYoungest = youngest.Count(p => p.Age == youngest); // performs query.

var youngestNames = children.Where(p => p.Age == youngest).Select(p => p.Name); // no query

var anArray = youngestNames.ToArray(); // performs query

string names = string.join(", ", anArray); // no query of course