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
Best Answer
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:
made my query fast again.
I found 2 ways to solve this:
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.