Slow LINQ Query

Question

I have a query that's running slow (in a loop of about 100 it takes 5-10 seconds) and have no clue why. It's simply querying against a List of objects... your help is much appreciated!

I'm basically querying for Schedules that have been assigned to specific managers. It must be from the specified Shifts week OR the first 2 days of next week OR the last 2 days of the previous week.

I tried calculating .AddDays before but that didn't help. When I ran a performance test it highlighted the "from" statement below.

List<Schedule> _schedule = Schedule.GetAll();
List<Shift> _shifts = Shift.GetAll();

// Then later...
List<Schedule> filteredSchedule = (from sch in _schedule 
                                    from s in _shifts
                                    where
                                        **sch.ShiftID == s.ShiftID
                                        & (sch.ManagerID == 1 | sch.ManagerID == 2 | sch.ManagerID == 3)
                                        & ((s.ScheduleWeek == shift.ScheduleWeek)
                                                | (s.ScheduleWeek == shift.ScheduleWeek.AddDays(7)
                                                        & (s.DayOfWeek == 1 | s.Code == 2))
                                                | (sch.ScheduleWeek == shift.ScheduleWeek.AddDays(-7)
                                                        & (s.DayOfWeek == 5 | s.Code == 6)))**
                                    select sch)
                                    .OrderBy(sch => sch.ScheduleWeek)
                                    .ThenBy(sch => sch.DayOfWeek)
                                    .ToList();

Jon Skeet · Accepted Answer · 2011-01-18 22:29:52Z

7

First port of call: use && instead of & and || instead of |. Otherwise all the subexpressions in the where clause will be evaluated, even if the answer is already known.

Second port of call: use a join instead of two "from" clauses with a where:

var filteredSchedule = (from sch in _schedule
                        join s in _shifts on s.ShiftID equals sch.ShiftID
                        where ... rest of the condition ...

Basically that's going to create a hash of all the shift IDs, so it can quickly look up possible matches for each schedule.

answered Jan 18, 2011 at 22:29

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

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

1 Comment

jon Over a year ago

Firstly, thank you Jon! I had it short circuiting before but didn't notice a difference. Using "join" instead of "where" did work though! I never knew LINQ treated the 2 differently. Secondly - wow! I feel like I just got help from a celebrity! :) I never read your book but have seen it plenty of times. You can be sure I'll be picking up a copy now though. :) p.s. if you're ever looking for a developer let me know! i.e. to co-author your next book! haha

Collectives™ on Stack Overflow

Slow LINQ Query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related