2
\$\begingroup\$

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 Presentation Layer

Business Layer Business Layer

Data Access 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?

\$\endgroup\$
1
  • \$\begingroup\$ Just to repeat a now deleted comment by @GeneS: Please run the SQL Profiler and post the SQL Statement that is being executed. \$\endgroup\$
    – svick
    Oct 20, 2012 at 0:47

1 Answer 1

1
\$\begingroup\$

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; 
}
\$\endgroup\$
1
  • \$\begingroup\$ By the way: I know it looks weird to create the query called allMilestones, but that's because of not reformatting your existing code to much. Basicly you can skip the first query and directly begin like this: var query = from m in _dataContext.Repository<Linq.TrackerMilestonesInSession>() \$\endgroup\$
    – Silvermind
    Oct 20, 2012 at 9:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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