Optimising foreach loops

Question

I have some code which pulls records out of the database and I'm then doing a simple comparison to check whether the records match a specific module and response and then using the count and displaying the statistic. Unfortunately it is very slow when it gets to anything greater than 1000 records in the database.

In nHibernate I turned on Lazy loading which helped a bit in the initial query time but when it gets to this part where it has to delve a little deeper it slows down a lot. I guess the problem is it's pulling data from a lot of different tables to get these statistics.

From some research it seems like I should be able to speed this up by writing linq statements instead of using the foreach loops but I've had a few tries and I'm not getting very far. I was wondering if anyone could help point me in the right direction. Or to a good Linq tutorial or book since I know little on the topic

Also I saw that in similar cases people have recommended putting a job into sql server to populate another table which is used for lookup but i'd like to avoid this if possible.

Here is the code.

int modules = 0;
var sessionsWithPullHits = from session in m_sessions where session.PullHits.Count > 0 select session;

foreach (ISession<PullHitRecord, PushHitRecord> session in sessionsWithPullHits)
{
    foreach (var pullHit in session.PullHits)

    if ((pullHit.Module == _Module) && (pullHit.Response == _response))
    {
        modules++;
    }
}

Many thanks for any help someone can give.

Answer 1

LINQ:

var modules = (from session in m_sessions
               from pullHit in session.PullHits
               where pullHit.Module == _Module && pullHit.Response == _response
               select pullHit).Count();

---

Note, i'm not sure how this would translate into SQL, but it's one LINQ statement, so should work.

Answer 2

@George Duckett's answer is spot on for replacing the foreach code with LINQ. Alternatively, you can express his same query in fluent syntax like so:

var modules = m_sessions
.SelectMany(session => session.PullHits, 
            (session, pullHits) => new { pullHits = pullHits })
.Where(session => session.pullHits.Module == _Module && 
                  session.pullHits.Response == _response)
.Count();

That ain't so pretty, but the resulting IL is smaller and may give you marginally better results. Also, this version gives slightly smaller code still and may brings small gains. Test and see:

var modules = m_sessions
  .Select(session => session.PullHits
      .Count(pullHit => pullHit.Module == _Module && 
                        pullHit.Response == _response))
  .Sum();

Disclaimer: I don't support premature optimization, I only enable it :-)