title: nhibernate query performance
body: <p>I decided to test nhibernate on an existing application (replacing linq to sql) and I'm seeing abysmal results so far. Hopefully someone here can point me in the right direction.</p>
  
  <p>My config:</p>
  
  <pre><code>&lt;hibernate-configuration xmlns="urn:nhibernate-configuration-2.2"&gt;
      &lt;session-factory&gt;
          &lt;property name="dialect"&gt;NHibernate.Dialect.MsSql2000Dialect&lt;/property&gt;
          &lt;property name="connection.provider"&gt;NHibernate.Connection.DriverConnectionProvider&lt;/property&gt;
          &lt;property name="connection.driver_class"&gt;NHibernate.Driver.SqlClientDriver&lt;/property&gt;
          &lt;property name="connection.connection_string"&gt;{my connection string here}&lt;/property&gt;
          &lt;property name="proxyfactory.factory_class"&gt;NHibernate.ByteCode.LinFu.ProxyFactoryFactory, NHibernate.ByteCode.LinFu&lt;/property&gt;
          &lt;property name="adonet.batch_size"&gt;10&lt;/property&gt;
          &lt;property name="show_sql"&gt;false&lt;/property&gt;           
          &lt;property name="use_outer_join"&gt;true&lt;/property&gt;
          &lt;property name="command_timeout"&gt;60&lt;/property&gt;
          &lt;property name="query.substitutions"&gt;false&lt;/property&gt;            
      &lt;/session-factory&gt;
  &lt;/hibernate-configuration&gt;
  </code></pre>
  
  <p>The domain class:</p>
  
  <pre><code>public class Code
  {
      public virtual Guid CodeId { get; set; }
      public virtual string CodeValue { get; set; }
      public virtual Guid EntryId { get; set; }
  }
  </code></pre>
  
  <p>The mapping xml:</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="utf-8" ?&gt;
  &lt;hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="SweepstakesWeb" namespace="SweepstakesWeb.Domain"&gt;
      &lt;class name="Code" table="Codes"&gt;
          &lt;id name="CodeId"&gt;
              &lt;generator class="guid.comb"/&gt;
          &lt;/id&gt;
          &lt;property name="CodeValue" length="20" /&gt;
          &lt;property name="EntryId" /&gt;
      &lt;/class&gt;
  &lt;/hibernate-mapping&gt;
  </code></pre>
  
  <p>When I run the test, I'm seeing this:</p>
  
  <p>For the following query:</p>
  
  <pre><code>SELECT this_.CodeId as CodeId0_0_, this_.CodeValue as CodeValue0_0_, this_.EntryId as EntryId0_0_ FROM Codes this_ WHERE this_.CodeValue = N'3734872FVD'
  </code></pre>
  
  <p>I get:</p>
  
  <pre><code>StmtText
  ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    |--Nested Loops(Inner Join, OUTER REFERENCES:([this_].[CodeID]) OPTIMIZED)
         |--Index Scan(OBJECT:([Codes].[ix_codes_code] AS [this_]),  WHERE:(CONVERT_IMPLICIT(nvarchar(20),[Codes].[CodeValue] as [this_].[CodeValue],0)=N'3734872FVD'))
         |--Clustered Index Seek(OBJECT:([Codes].[PK__Codes__3493CFA7] AS [this_]), SEEK:([this_].[CodeID]=[Codes].[CodeID] as [this_].[CodeID]) LOOKUP ORDERED FORWARD)
  </code></pre>
  
  <p>If I manually change the parameter to varchar (removing the N prefix) like so:</p>
  
  <pre><code>SELECT this_.CodeId as CodeId0_0_, this_.CodeValue as CodeValue0_0_, this_.EntryId as EntryId0_0_ FROM Codes this_ WHERE this_.CodeValue = '3734872FVD'
  </code></pre>
  
  <p>I get:</p>
  
  <pre><code>StmtText
  ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    |--Nested Loops(Inner Join, OUTER REFERENCES:([this_].[CodeID]) OPTIMIZED)
         |--Index Seek(OBJECT:([Codes].[ix_codes_code] AS [this_]), SEEK:([this_].[CodeValue]='3734872FVD') ORDERED FORWARD)
         |--Clustered Index Seek(OBJECT:([Codes].[PK__Codes__3493CFA7] AS [this_]), SEEK:([this_].[CodeID]=[Codes].[CodeID] as [this_].[CodeID]) LOOKUP ORDERED FORWARD)
  </code></pre>
  
  <p>Note that with varchar parameter values instead of nvarchar, I get an index seek. With nvarchar, I get an index scan. The column type is varchar(20). Apparently, there's an implicit conversion going on that is causing a full scan instead of a seek. </p>
  
  <ol>
  <li>Why does the implicit conversion require a scan instead of a seek?</li>
  <li>Why does nhibernate use nvarchar by default, and how can I tell it to use varchar?</li>
  </ol>