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<Data>()
    .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?

Answer 1

Sinces there is not much information about your application, I only can guess.

Performance problems with NH typically occur caused by flushing the cache. The cache is flushed before each query. When there are lots of entities in the session, it may take quite a lot of time. Try the following:

Log.Info("Flushing");
Session.Flush();
Session.FlushMode = FlushMode.Never;

Log.Info("Query");
var list = Session.Linq<Data>()
    //...
Log.Info("Done");
// for production code, this belongs into a finally block
Session.FlushMode = FlushMode.Auto; 

If it actually is a flushing problem, you need to flush manually on certain points in the transaction. Be careful when turning off auto flush. It may cause ugly side effects. It is very specific to your transaction and I can't say you how to implement it the right way. You could also use a StatelessSession, but for me it never worked (it has some limits). You may also clear the session, which also requires that you exactly know what you are doing.

If it is no flushing problem, it gets difficult to track. Use Profiler to see if it actually takes the time in the SQL server query. It may even be a caching problem on SQL server. In this case it takes minutes the first time you execute the query, but only seconds the second time. Creating proper indexes may help. Here I stop guessing...

Answer 2

My assumption that there is some interceptors that slow down objects materialization or eager loading(i.e. N+1 problem).

I've done some test and even 30 000 objects cannot slow down getting list of objects(from local machine 500ms to get list of 30000 objects, from remote db - 4 seconds).