CODEWITHSUNDEEP
c#linqentity-framework
Alex
Alex

Reputation: 145

very slow IOrderedQueryable ToList()

I have the query that returns parent with filtered child's:

 Context.ContextOptions.LazyLoadingEnabled = false;
  var query1 = (from p in Context.Partners
                          where p.PartnerCategory.Category == "03"
                                || p.PartnerCategory.Category == "02"
                          select new
                                     {
                                         p,
                                         m = from m in p.Milk
                                             where m.Date >= beginDate
                                                   && m.Date <= endDate
                                                   && m.MilkStorageId == milkStorageId
                                             select m,
                                         e = p.ExtraCodes,
                                         ms = from ms in p.ExtraCodes
                                              select ms.MilkStorage,
                                         mp = from mp in p.MilkPeriods
                                              where mp.Date >= beginDate
                                                    && mp.Date <= endDate
                                              select mp
                                     }).Where(
                                         p =>
                                         p.p.ExtraCodes.Select(ex => ex.MilkStorageId).Contains(
                                             milkStorageId) ).OrderBy(p => p.p.Name);
 var partners = query1.AsEnumerable().ToList();

Query return 200 records and converting from IOrderedQueryable ToList() is very slow. Why?

After profiling query in sql server management studio i've noticed that query execute's 1 second and returns 2035 records.

Upvotes: 1

Views: 1791

Answers (3)

Rune FS
Rune FS

Reputation: 21742

There could be a number of reasons for this and without any profiler information it's just guess work and even highly educated guess work by some one that knows the code and domain well is often wrong.

You should profile the code and since it's likely that the bottleneck is in the DB get the command text as @Likurg suggests and profile that in the DB. It's likely that you are missing one or more indexes.

There's a few things you could do to the query it self as well if for nothing else to make it easier to understand and potentially faster

E.g.

p.p.ExtraCodes.Select(ex => ex.MilkStorageId).Contains(milkStorageId)

is really

p.p.ExtraCodes.Any(ex => ex.MilkStorageId == milkStorageId)

and could be moved to the first where clause potentially lowering the number of anonymously typed objects you create. That said the most likely case is that one of the many fields you use in your comparisons are with out an index potentially resulting in a lot of table scans for each element in the result set.

Some of the fields where an index might speed things up are

  • p.p.Name
  • m.Date
  • m.MilkStorageId
  • mp.Date
  • PartnerCategory.Category

Upvotes: 1

Likurg
Likurg

Reputation: 2760

At first, look at the generated query by using this

Context.GetCommand(query1).CommandText;

then invoke this command in db. And check how many records reads by profiler.

Upvotes: 0

Habib
Habib

Reputation: 223187

The reason it is slow is because when you do ToList that is the time when the actual query execution takes place. This is called deferred execution.

You may see: LINQ and Deferred Execution

I don't think you need to do AsEnumerable when converting it to a list, you can do it directly like:

var partners = query1.ToList();

Upvotes: 1

Related Questions