NHibernate SQL query slow

Question

I am using LINQ to NH to get a bunch of data on app startup. I specifically added ToList() to force immediate query execution:

Group group = GetGroup();
Log.Info("started");
var list = Session.Linq()
    .Where(p => p.Group.Id == group.Id)
    .OrderByDescending(p => p.Stamp.Counter) /* Stamp is composite mapping */
    .Select(p => new
    {
        Counter = p.Stamp.Counter,
        Status = p.Status,
    })
    .Take(4000)
    .ToList();
Log.Info("done");

Checking the DEBUG log for NHibernate.SQL logger gives the following SQL, as expected (and this same query pops out in SQL Profiler, when I start monitoring):

SELECT top 4000 this_.Counter as y0_, this_.Status as y1_
FROM [Data] this_ 
LEFT OUTER JOIN [Group] group1_ ON this_.Group_id=group1_.Id
WHERE group1_.Id = @p0 
ORDER BY this_.Counter desc; @p0 = 1

The problem is that this query takes 2 minutes to complete when invoked from my app, compared to 0.5s when executed in SSMS! Actually, while the app is waiting for the query to complete, I can execute it in SSMS and get results instantly.

Where do you think this difference comes from?

NHibernate SQL query slow

4 Answers