title: Query executed from Nhibernate is slow, but from ADO.NET is fast
body: <p>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.</p>
  
  <p>I've seen similiar questions on SO about problems similar to this one, so I took my test one step further.</p>
  
  <p>I intercepted the query using Sql Server Profiler, and executed the query using ADO.NET in my application.</p>
  
  <p>The query that i got from the Profiler is something like: "exec sp_executesql N'select...."</p>
  
  <p>My ADO.NET code:</p>
  
  <pre><code>SqlConnection conn = (SqlConnection) NHibernateManager.Current.Connection;
  
  var query = @"&lt;query from profiler...&gt;";
  var cmd = new SqlCommand(query, conn);
  
  SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
  return RedirectToAction("Index");
  </code></pre>
  
  <p>This query is also very fast, taking no time to execute.</p>
  
  <p>Also, I've seen something very strange on the Profiler. The query, when executed from NH, has the following statistics:</p>
  
  <p>reads: 281702
  writes: 0</p>
  
  <p>The one from ADO.NET:</p>
  
  <p>reads: 333
  writes: 0</p>
  
  <p>Anyone has any clue? Is there any info I may provide to help diagnose the problem?</p>
  
  <p>I thought it might be related to some connection settings, but the ADO.NET version is using the same connection from NHibernate.</p>
  
  <p>Thanks in advance</p>
  
  <p>UPDATE:</p>
  
  <p>I'm using NHibernate LINQ. The query is enormous, but is a paging query, with just 10 records being fetched.</p>
  
  <p>The parameters that are passed to the "exec sp_executesql" are:</p>
  
  <p>@p0 int,@p1 datetime,@p2 datetime,@p3 bit,@p4 int,@p5 int</p>
  
  <p>@p0=10,@p1='2009-12-01 00:00:00',@p2='2009-12-31 23:59:59',@p3=0,@p4=1,@p5=0</p>