How to improve the LINQ query performance within a for loop

shabnamzangeneh · July 22nd, 2014, 11:50 AM

Hi,

I am trying to improve the speed of my application by getting rid of the LINQ queries inside my for loops (very inefficient). Do you know the best way to do it? Should I define a local IQueryable equal to the LINQ query result and then read data from that object?

Here is a piece of my code where I have loop of a LINQ query (For Each wkend ) within another for loop (For j). It is very slow.

Code:

wkendQuery = From wkendSchedule As MilestoneWeekendSchedule In eReportContext.MilestoneWeekendSchedules Where wkendSchedule.milestoneStatusID.Equals(thisMilestoneID)

For j = 0 To dgvWaterfall.Columns.Count - 1
                If wkendQuery.ToArray.Length >= 0 Then
                    For Each wkend In wkendQuery.ToArray
                        If wkend.weekendDate = dgvWaterfall.Columns(j).HeaderText Then
                                 ...
                        End if
                    next wkend
                End if
next j

Thanks very much for your help and support.

Rod Stephens · July 22nd, 2014, 05:02 PM

Yes, LINQ can be inefficient, particularly if it's inside a loop as it is here.

Quote:

Should I define a local IQueryable equal to the LINQ query result and then read data from that object?

That's probably a good idea.

The wkendQuery query doesn't change inside the loop. But when you execute its ToArray method (inside the loop), it executes the query and moves the result into an array each time. You might think it would be smart enough to keep the array around, but it's not and rebuilds it each time.

So you can probably get a big improvement if you just move the ToArray call outside of the loop. Something like this:

Code:

wkendQuery = From wkendSchedule As MilestoneWeekendSchedule
     In eReportContext.MilestoneWeekendSchedules
     Where wkendSchedule.milestoneStatusID.Equals(thisMilestoneID)
MilestoneWeekendSchedule[] weekends = wkendQuery.ToArray()

For j = 0 To dgvWaterfall.Columns.Count - 1
    If wkendQuery.ToArray.Length >= 0 Then
        For Each wkend In weekends
            If wkend.weekendDate = dgvWaterfall.Columns(j).HeaderText Then
                ...
            End If
        Next wkend
    End If
Next j

If that isn't a big enough improvement, then you'll need to look at other ways to store the milestone weekend schedules from which you're pulling data. For example, if you store the data in a sorted list or database with an index on the milestoneStatusID field, you may be able to find the data you need more quickly than the sequential search that LINQ is probably performing.

(My guess is you'll see a big improvement just by moving the call to ToArray.)

shabnamzangeneh · July 22nd, 2014, 07:01 PM

Quote:

My guess is you'll see a big improvement just by moving the call to ToArray.

That was a BIG improvement (~80%).

Thanks for your help.

mmorgan30 · July 30th, 2014, 12:49 AM

I am better with c# so ill show in c#. Not knowing exactly what your gridview looks like, I am assuming its dates as column headers based on your for loops. I am also free handing this, so it may contain some compile errors. The point is how i am doing it.

Code:


// Select all column header text as dates
var gvColumnDates = dgvWaterfall.Columns.Select( c => Convert.ToDateTime(c.HeaderText) );

// now select the schedules that have dates in the gridview headers that have someId as milestonestatus id
var queryResults = eReportContext.MilestoneWeekendSchedules.Where( s => s.MilestoneStatusId = someid && gvColumnDates.Contains( s.WeekendDATE ) ).ToArray();