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

Question

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

Answer 1

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.

Answer 2

I had the exact same problem as the OP. I tried @psousa's suggestion of injecting an "option(recompile)" which did improve my performance. But in the end I found that simply updating statistics on SQL Server did the trick for me.

update statistics tablename;

I ended up backing out my code to inject the "option(recompile)". I realize this may not be the answer for everyone, but wanted to share since it was the cause of my problems.

Answer 3

Look at the parameters being supplied to the sp_executesql stored proc. If the parameters are supplied as nvarchar (N'value') and the columns they reference are varchar, SQL Server will use a very inefficient query plan. This has been the root cause of all the performance issues I've had that exhibit these symptoms (slow in app., fast in SSMS).