Query executed from Nhibernate is slow, but from ADO.NET is fast

Asked 2011-10-13T11:46:50.133

I have a query in my MVC application which takes about 20 seconds to complete (using NHibernate 3.1). When I execute the query manually on Management studio it takes 0 seconds.

I've seen similiar questions on SO about problems similar to this one, so I took my test one step further.

I intercepted the query using Sql Server Profiler, and executed the query using ADO.NET in my application.

The query that i got from the Profiler is something like: "exec sp_executesql N'select...."

My ADO.NET code:

SqlConnection conn = (SqlConnection) NHibernateManager.Current.Connection;

var query = @"<query from profiler...>";
var cmd = new SqlCommand(query, conn);

SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return RedirectToAction("Index");

This query is also very fast, taking no time to execute.

Also, I've seen something very strange on the Profiler. The query, when executed from NH, has the following statistics:

reads: 281702 writes: 0

The one from ADO.NET:

reads: 333 writes: 0

Anyone has any clue? Is there any info I may provide to help diagnose the problem?

I thought it might be related to some connection settings, but the ADO.NET version is using the same connection from NHibernate.

Thanks in advance

UPDATE:

I'm using NHibernate LINQ. The query is enormous, but is a paging query, with just 10 records being fetched.

The parameters that are passed to the "exec sp_executesql" are:

@p0 int,@p1 datetime,@p2 datetime,@p3 bit,@p4 int,@p5 int

@p0=10,@p1='2009-12-01 00:00:00',@p2='2009-12-31 23:59:59',@p3=0,@p4=1,@p5=0

Edit Report

2 Answers

I had the ADO.NET and NHibernate using different query-plans, and I was sufering the effects of parameter sniffing on the NH version. Why? Because I had previously made a query with a small date interval, and the stored query-plan was optimized for it.

Afterwards, when querying with a large date interval, the stored plan was used and it took ages to get a result.

I confirmed that this was in fact the problem because a simple:

DBCC FREEPROCCACHE -- clears the query-plan cache

made my query fast again.

I found 2 ways to solve this:

Injecting an "option(recompile)" to the query, using a NH Interceptor
Adding a dummy predicate to my NH Linq expression, like: query = query.Where(true) when the expected result-set was small (date interval wise). This way two different query plans would be created, one for large-sets of data and one for small-sets.

I tried both options, and both worked, but opted for the second approach. It's a little bit hacky but works really well I my case, because the data is uniformly distributed date-wise.

answered 2011-10-21T17:27:44.463

you didn't specify your query or the size of its resultset, but there's an issue with fetching large number of entities with nHibernate.
basically, the time to 'hydrate' the objects is what's taking that long.
you can try turning on the reflection optimizer, or using an IStatelessSession.
see som suggestions that i've got here.

answered 2011-10-13T14:53:22.117

Query executed from Nhibernate is slow, but from ADO.NET is fast

2 Answers

Your Answer