Simple retrieving sessionId rows from indexed SQL column is slow

Question

I have to select all the rows from a database table containing a defined (long)sessionId where the sessionId row is indexed. But it is slow, and since the code to access it is really simple, I'm wondering where the problem is. Here is the code of the three layers:

var localPath = BusinessClient.Instance.Tracker.GetSpecifiedMilestonesInSessionObjects(milestonesInSession.SessionId).ToList();

public IQueryable<MilestonesInSession> GetSpecifiedMilestonesInSessionObjects(long sessionId)
    {
        var query = from m in _milestonesInSessionRepository.GetAll()
                    where m.SessionId == sessionId
                    select m;

        return query;
    }

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

Here attached the screenshot of the performance using ANTS:

Presentation Layer

Business Layer

Data Access Layer

The sessionId in the database is not unique, but I created an index on them, why accessing it is not an instant operation?

Thanks in advance.

Answer 1

I think the bottleneck is in IQueryable<Model.Tracker.MilestonesInSession> GetAll() because it looks like you are executing that query into memory by creating a new Model.Tracker.MilestonesInSession Try this:

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