Getting instant/better result instead of waiting at least 10 seconds every time

I have the following code that unfortunately is really slow:

private void FilterSessionByDate()
{
SessionsFilteredByDate =
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 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;
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 &&
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
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:

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

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

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.

• Hello, The GetAllMilestonesInSessionObjects() is returning an IQueryable and I have an index on the sessionId Oct 11, 2012 at 8:27
• 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)? Oct 11, 2012 at 13:32
• I posted the content of the GetAllMilestonesInSessionObjects method. A sessionId matches exactly 10 records. The sessionId cannot be null anywhere. Oct 11, 2012 at 13:51
• 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? Oct 11, 2012 at 14:11
• 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... Oct 11, 2012 at 14:28

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.

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.

• 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 :\ Oct 12, 2012 at 6:32
• You'll always have to 'code it' - just code it better. Oct 12, 2012 at 12:31