7
\$\begingroup\$

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.

IQueryable

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

  return query;

}

MilestonesInSession

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;
  }

Ants Performance profiler screenshots:

Ants Performance profiler screenshot 01

Ants Performance profiler screenshot 02

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

\$\endgroup\$
1
  • 1
    \$\begingroup\$ Is that method .GetAllMilestonesInSessionObjects() lazy-loading, or does it get all Milestones from the DB and then the .Where() filters them? \$\endgroup\$
    – ANeves
    Oct 10, 2012 at 15:36

3 Answers 3

6
\$\begingroup\$

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.

\$\endgroup\$
7
  • \$\begingroup\$ Hello, The GetAllMilestonesInSessionObjects() is returning an IQueryable and I have an index on the sessionId \$\endgroup\$
    – Attila
    Oct 11, 2012 at 8:27
  • \$\begingroup\$ So, is BusinessClient.Instance.Tracker.GetAllMilestonesInSessionObjects and autogenerated Linq2SQL method? It might be worth looking at the contents of this method. How many records match a particular session id? Also, can session id be null (both in the table and in the parameter)? \$\endgroup\$
    – Griffin
    Oct 11, 2012 at 13:32
  • \$\begingroup\$ I posted the content of the GetAllMilestonesInSessionObjects method. A sessionId matches exactly 10 records. The sessionId cannot be null anywhere. \$\endgroup\$
    – Attila
    Oct 11, 2012 at 13:51
  • \$\begingroup\$ As straightforward as expected. The query should end up being "select [columns] from milestoneSession where sessionId = @p0" (executed with sp_executesql). What is the datatype of session id? is the code consistent with the database? \$\endgroup\$
    – Griffin
    Oct 11, 2012 at 14:11
  • \$\begingroup\$ the datatype of the session Id is a long, and it is a autoincrement number, the code is consistent with the database. I don't know about the select operation you wrote... \$\endgroup\$
    – Attila
    Oct 11, 2012 at 14:28
3
\$\begingroup\$

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.

\$\endgroup\$
0
\$\begingroup\$

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.

\$\endgroup\$
2
  • \$\begingroup\$ Hello, Thanks for your post, I thought about having a single query or a single linq query to achieve the same, but nothing concrete came to my mind, so I had to code it :\ \$\endgroup\$
    – Attila
    Oct 12, 2012 at 6:32
  • \$\begingroup\$ You'll always have to 'code it' - just code it better. \$\endgroup\$ Oct 12, 2012 at 12:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.