title: Nhibernate query times out while sql query not
body: <p>I'm running this code to fetch data:</p>
  
  <pre><code>var docs = session.Query&lt;Content&gt;().Where(x =&gt;  x.Attachments.Count &gt; 0).Skip(pageNumber * Pagesize).Take(count).ToList();
  </code></pre>
  
  <p>It should get all documents with attachements. I added paging to boost it up, and not taking all documents at one step (there is about 10k documents matching and 600k all). </p>
  
  <p>Query executed by NHibernate:</p>
  
  <pre><code>exec sp_executesql N'SELECT TOP (@p0) CastleId12_, Version12_, Abstract12_, Publishe4_12_, Title12_, Body12_, Brand12_, Source12_, SourceCo9_12_, IdInSource12_, Documen11_12_, HTML12_, Subscri13_12_, FileLoc14_12_, OtherMe15_12_, Companies12_, Keywords12_, Subscri18_12_, Author12_, Documen20_12_, SourceF21_12_, SourceB22_12_, UpdateDate12_, SourceU24_12_, Content25_12_, Interna26_12_, Workben27_12_, Checksum12_, Field29_12_ FROM (select content0_.CastleId as CastleId12_, content0_.Version as Version12_, content0_.Abstract as Abstract12_, content0_.PublishedDate as Publishe4_12_, content0_.Title as Title12_, content0_.Body as Body12_, content0_.Brand as Brand12_, content0_.Source as Source12_, content0_.SourceContentId as SourceCo9_12_, content0_.IdInSource as IdInSource12_, content0_.DocumentType as Documen11_12_, content0_.HTML as HTML12_, content0_.Subscriptions as Subscri13_12_, content0_.FileLocation as FileLoc14_12_, content0_.OtherMetadata as OtherMe15_12_, content0_.Companies as Companies12_, content0_.Keywords as Keywords12_, content0_.SubscriptionUpdateDate as Subscri18_12_, content0_.Author as Author12_, content0_.DocumentStatus as Documen20_12_, content0_.SourceFileExtension as SourceF21_12_, content0_.SourceBaseName as SourceB22_12_, content0_.UpdateDate as UpdateDate12_, content0_.SourceUpdateDate as SourceU24_12_, content0_.ContentUpdateDate as Content25_12_, content0_.InternalDocumentType as Interna26_12_, content0_.WorkbenchList as Workben27_12_, content0_.Checksum as Checksum12_, content0_.Field_id as Field29_12_, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row from [Content] content0_ where (select cast(count(*) as INT) from ContentAttachments attachment1_ where content0_.CastleId=attachment1_.DocumentId)&gt;@p1) as query WHERE query.__hibernate_sort_row &gt; @p2 ORDER BY query.__hibernate_sort_row',N'@p0 int,@p1 int,@p2 int',@p0=50,@p1=0,@p2=0
  </code></pre>
  
  <p>If I run it in that way it takes 1.5 minutes to execute in SSMS and of course it throws timeout exception in my app. </p>
  
  <p>If I change it to:</p>
  
  <pre><code>SELECT TOP (50) CastleId12_, Version12_, Abstract12_, Publishe4_12_, Title12_, Body12_, Brand12_, Source12_, SourceCo9_12_, IdInSource12_, Documen11_12_, HTML12_, Subscri13_12_, FileLoc14_12_, OtherMe15_12_, Companies12_, Keywords12_, Subscri18_12_, Author12_, Documen20_12_, SourceF21_12_, SourceB22_12_, UpdateDate12_, SourceU24_12_, Content25_12_, Interna26_12_, Workben27_12_, Checksum12_, Field29_12_ FROM (select content0_.CastleId as CastleId12_, content0_.Version as Version12_, content0_.Abstract as Abstract12_, content0_.PublishedDate as Publishe4_12_, content0_.Title as Title12_, content0_.Body as Body12_, content0_.Brand as Brand12_, content0_.Source as Source12_, content0_.SourceContentId as SourceCo9_12_, content0_.IdInSource as IdInSource12_, content0_.DocumentType as Documen11_12_, content0_.HTML as HTML12_, content0_.Subscriptions as Subscri13_12_, content0_.FileLocation as FileLoc14_12_, content0_.OtherMetadata as OtherMe15_12_, content0_.Companies as Companies12_, content0_.Keywords as Keywords12_, content0_.SubscriptionUpdateDate as Subscri18_12_, content0_.Author as Author12_, content0_.DocumentStatus as Documen20_12_, content0_.SourceFileExtension as SourceF21_12_, content0_.SourceBaseName as SourceB22_12_, content0_.UpdateDate as UpdateDate12_, content0_.SourceUpdateDate as SourceU24_12_, content0_.ContentUpdateDate as Content25_12_, content0_.InternalDocumentType as Interna26_12_, content0_.WorkbenchList as Workben27_12_, content0_.Checksum as Checksum12_, content0_.Field_id as Field29_12_, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row from [Content] content0_ where (select cast(count(*) as INT) from ContentAttachments attachment1_ where content0_.CastleId=attachment1_.DocumentId)&gt;0) as query WHERE query.__hibernate_sort_row &gt; 0 ORDER BY query.__hibernate_sort_row 
  </code></pre>
  
  <p>(The only change is that I don't run it in sp_executesql and I'm inlining parameters)
  It takes about 2 seconds to execute. </p>
  
  <p><strong>Has anyone any idea how can I modify my Nhibernate query so it will work faster?</strong> I tried changing page size and page, but nothing changed. I read about <code>sp_executesql</code> but the only thing I found is about <code>varchar</code> arguments: <a href="https://stackoverflow.com/a/4540108/1714342">https://stackoverflow.com/a/4540108/1714342</a> </p>
  
  <p>Why SSMS is executing it so slow? </p>