How to optimize this code to get a instant/better result instead of waiting at least 10 seconds every time?

Question

I have the following code that unfortunately is really slow:

private void FilterSessionByDate()
{
  SessionsFilteredByDate =
    BusinessClient.Instance.Tracker.GetAllMilestonesInSessionObjects().Where(
                i => i.CreatedDate >= GetFromDate() && i.CreatedDate <= GetToDate());
}

private void FilterSessionsByTrackerId()
{
  SessionsFilteredByDateAndTrackerId =
    SessionsFilteredByDate.Where(i=>i.TrackerId == CurrentItem.ID);
}

private void BindGrid(DateTime fromDate, DateTime toDate, int trackerId, int campaignId)
{
  var result = BusinessClient.Instance.Tracker.GetReportForCampaign(trackerId, campaignId, fromDate, toDate);
  foreach (var trackerReport in result)
  {
    trackerReport.Errors = GetErrors(trackerReport);
  }
  StatisticsGrid.DataSource = result.Where(r => r.ParentMilestoneId == null);
  StatisticsGrid.DataBind();
}

private int GetErrors(TrackerReport milestone)
{
  int counter = 0;

  //var milestonesWithId = BusinessClient.Instance.Tracker.GetAllMilestonesInSessionWithTrackerId(CurrentItem.ID);
  //var sessionsWithDateRange = milestonesWithId.Where(i=> i.CreatedDate >= GetFromDate() && i.CreatedDate <= GetToDate());
  var sessionsWithStatusError = SessionsFilteredByDateAndTrackerId.Where(i => i.StatusId == 0);

  foreach (var milestonesInSession in sessionsWithStatusError)
  {
    // Get all milestoneinsessions with this session
    MilestonesInSession session = milestonesInSession;
    var localPath = BusinessClient.Instance.Tracker.GetAllMilestonesInSessionObjects().Where(
                    i => i.SessionId == session.SessionId).ToList();

    var lastStep = localPath.Max(i => i.MilestoneId);
    var latestTime = localPath.Where(i => i.MilestoneId != lastStep).Max(i=>i.CreatedDate);
    var milestoneWithLatestTime = localPath.First(x => x.CreatedDate == latestTime);

    if (milestonesInSession.MilestoneId != milestone.MilestoneId)
    {
      if (milestoneWithLatestTime.MilestoneId == milestone.MilestoneId &&
          milestoneWithLatestTime.CreatedDate.AddSeconds(2) >= milestonesInSession.CreatedDate)
        counter++;
    }
    else 
    {
      // It is the last milestone, we have to check if the milestone with
      // the latest time is close to this one, otherwise the error happened
      // at the last step
      if (milestonesInSession.CreatedDate > milestoneWithLatestTime.CreatedDate.AddSeconds(2))
        counter++;
    }
  }

  return counter;
}

The MilestonesInSessions in the database are 2 millions, but I managed to optimize the queries, so that the retrieving of them are pretty fast.
Anyway, having multiple nested foreach affects its speed.

Given that:
The result variable is always with 10 entries.
The sessionStatusWithError variable has generally around 100 entries.
The localpath variable is always with 10 entries.

Ants Performance profiler screenshots:

My question:
Do you know how can I optimize it to get a instant/better result instead of waiting at least 10 seconds every time?

Edit:

I added the businesslogic methods:

[RequiresDataAccessSynchronized]
public IQueryable<MilestonesInSession> GetAllMilestonesInSessionObjects()
{
  var query = from m in _milestonesInSessionRepository.GetAll()
              select m;

  return query;

}

and:

public class MilestonesInSession : DataAccessBase2, IDataOperations<Model.Tracker.MilestonesInSession, Model.Tracker.MilestonesInSession>
{
  #region IDataOperations<MilestonesInSession,int> Members

  public IQueryable<Model.Tracker.MilestonesInSession> GetAll()
  {
    var query = from milestoneSession in     _dataContext.Repository<Linq.TrackerMilestonesInSession>()
    select new Model.Tracker.MilestonesInSession
    {
      MilestoneId = milestoneSession.MilestoneId,
      CreatedDate = milestoneSession.CreatedDate,
      SessionId = milestoneSession.SessionId,
      ProductId = milestoneSession.ProductId,
      TrackerId = milestoneSession.TrackerId,
      StatusId = milestoneSession.StatusId,
      BankId = milestoneSession.BankId
    };
    return query;
  }

Answer 1

If BusinessClient.Instance.Tracker.GetAllMilestonesInSessionObjects() is not returning IQueryable, you will be loading up all results from that method before applying the Where filters - on each iteration through the for loop. If that method really does what it says, then you are talking about transmitting millions of records from the DB before filtering, over and over.

On the other hand, if BusinessClient.Instance.Tracker.GetAllMilestonesInSessionObjects() is returning IQueryable, then are you sure the underlying table is indexed for a comparison on the SessionId field?

Either way, the ANTS performance readout makes it clear that's the bottle-neck.

Answer 2

You may also want to look at the MiniProfiler as it may give you more insight at a view that is optimized for both ASP.Net and DB query level (with a wrapper for Linq2Sql or EF). It appears to be making several trips to the data source so any slowness there will be magnified and could be exposed with this tool.

It was created originally for MVC but also works with WebForms.

Answer 3

This looks like a classic iterative query problem.

Instead of thinking procedurally (first do this, then do that) instead try think of what you need to do altogether, or set-based (count the items when this and that and the other then...). SQL works much better when given set based queries.

I don't want to rewrite your query for you, but it appears that you are attempting to count how many milestones with session errors (where their last time is not equal to their last step) were within two seconds of the given miltestone. You should be able to write that in a single SQL query, rather than retrieving items, counting them, sorting them, and repeating n times.