title: Slow query in MS-SQL + Fluent NHibernate
body: <p>TL;DR: A query using wildcards/like takes four times longer to complete in Fluent NHibernate than executing exactly the same query using Management Studio. This only happens in MS-SQL, while Postgres works just fine. They query only returns two hits.</p>
  <p>I'm a querying a 100 million rows table using wildcards (like operator) using Fluent NHibernate, like this:</p>
  <pre><code>    return await foobarQuery.Select(e =&gt; e.Id).ToListAsync();
  </code></pre>
  <p>I have console logged the produced SQL Query to be this:</p>
  <pre><code>select
          foobar0_.[Id] as col_0_0_
      from
          [FooBar] foobar0_
      where
          exists (
              select
                  foobarfi1_.[Id]
              from
                  [FooBarFile] foobarfi1_
              where
                  foobar0_.[Id]=foobarfi1_.[FooBarId]
                  and (
                      foobarfi1_.[FileName] like '%Lorem Ipsum%'
                  )
          )
      order by
          foobar0_.[Id] asc;
  </code></pre>
  <p>When I execute this in Microsoft SQL Management Studio it takes approx 30 seconds. But the NHibernate query takes over two minutes to complete! It only returns two hits, so the culprit cannot be the handling of the returned data.</p>
  <p>To make things even more interesting, when I try exactly the same thing in a Postgres database, it takes approx 15 seconds to complete, both in pgAdmin and from my Fluent NHibernate query. (I guess that I have to live with the fact that Postgres i three times faster, this question is why the NHibernate query is so much slower.)</p>
  <p>So the question is, what can cause MS SQL + Fluent NHibernate to become this slow? It takes almost four times as long as a direct query in Management Studio, while there is almost no equivalent overhead when using Postgres.</p>
  <p>Sample Code:</p>
  <pre><code>public static async Task&lt;ICollection&lt;int&gt;&gt; GetFooBarIdsAsync(ISession session)
  {
      Expression&lt;Func&lt;FooBarFile, bool&gt;&gt; predicate = null;
      predicate = predicate.OR(ef =&gt; ef.FileName.Like(&quot;%lorem Ipsum%&quot;));
  
      var foobars = session.Query&lt;FooBar&gt;();
      foobars = foobars.Where(e =&gt; e.FooBarFiles.AsQueryable().Any(predicate));
  
      var sw = Stopwatch.StartNew();
      var result = await foobars.Select(e =&gt; e.Id).ToListAsync();
      var time = sw.Elapsed;
  
      return result;
  }
  </code></pre>